dcsimg
www.webdeveloper.com
Results 1 to 2 of 2

Thread: MySQL question

Hybrid View

  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
    21,295
    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).
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

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