I have a two tables called
- space (id, title, type, created_on)
- space_counter (id, space_id, imp_count, clk_count, created_on)
basically space is an advertising space and counter is a table where daily counts for that space are calculated.
So if for example we have 10 spaces. Every day a cron job immediately at 00:00:00 creates a new record in the space_counter for each space (therefore 10 are created).
All day when people see these boxes the system adds +1 to impression, or if clicked +1 to clicks.
This has been going well but now I have over 55,000 records on the space_counter and the system is becoming very very slow for counting impressions.
This is the query I created:
Considering today is 2013-01-21 as it needs to review all the records.
UPDATE `space_counter` SET `imp_count` = imp_count+1 WHERE `space_id` = '1969' AND AND `created_on` > '2013-01-21 00:00:00'
What is the correct and optimal way of doing this? How can I have the system ONLY check the top ones and stop checking immediately when `created_on` becomes 2013-01-20 ...
Do you have indexes on space_id and created_on? (55,000 rows is not really all that much, if things are properly optimized (which mostly means indexing any columns used in where clauses or joins).
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)