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).