MySQL question

I have a two tables called

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’[/CODE]

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 …

Thanks.

Also in #PHP _↴