www.webdeveloper.com
Results 1 to 2 of 2

Thread: MySQL question

  1. #1
    Join Date
    Aug 2005
    Posts
    220

    MySQL question

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

    Thanks.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,388
    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

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles