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:
Code:
UPDATE `space_counter` SET `imp_count` = imp_count+1 WHERE `space_id` = '1969' AND AND `created_on` > '2013-01-21 00:00:00'
Considering today is 2013-01-21 as it needs to review all the records.
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).
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Reply With Quote
Bookmarks