SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Switzerland
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i have a problem with a script that i have troubles to find how to optimize. basically it's a small (but important) redirection script that count the amount of clicks made every day. it's used A LOT ( about 70'000 times a day ) and it does this:

    1. read how many clicks where already made today (SELECT)
    2. if none, create a new entry, for today (INSERT)
    3. take the clicks variable and add 1 ($clicks++)
    4. update the clicks variable on the database (UPDATE)

    the problem is that when a table is being updated, it cannot be read so in the end, there's a very long queue of SELECT queries waiting for the UPDATEs to be finished and it makes the script VERY slow, even if it's very simple.

    anyone has an idea about how to solve this problem and optimize this script?? i've started to read this big MySQL book but it will take time and i really need to find a solution. i'm sure there must be solutions? maybe storing on a text file would be faster? or not? or creating smaller tables? is there any way to go around the fact a table being updated cannot be read?

    i'm desesperated

    thanks a lot in advance


    zEBz

  2. #2
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i assume you have indexes?

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Switzerland
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes. is that good or bad? i read that indexes mustn't always be used, but haven't had time yet to understand really this issue.

  4. #4
    SitePoint Zealot
    Join Date
    May 2000
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, can you combine the SELECT and UPDATE?

    1. UPDATE table SET clicks = clicks + 1 WHERE (condition)

    2. If 0 rows were affected, create a new entry.


    Perhaps you can also split the one table, to two or more tables? Would that help?

    Arpith

  5. #5
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    pasting the actual code of the script here might help.

    Yes you would need indexes here if you are doing a query such as SELECT from

    actually, replacing the SELECT with COUNT should help i think....

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Switzerland
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i didn't know you coud combine the SELECT and the UPDATE code like this! it will make the code cleaner but since the query needs to read a data (to know how much to increment) i take it that it won't really solve the problem?



    i'm starting to think about something: instead of storing daily data like this, would i have better to insert everything in a bulk using an INSERT comming without reading any data and then every day at midnight, take this huge table (well, 70000 entries), adding the various rows together, store this on a different table and then empty the first table? that way, i would add the numbers only once per day, using a simple COUNT statement? i never thought that using more tables could actually make things faster do you think it would work better?

  7. #7
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dont make different tables. Simply include a datestamp so you can sort by date.

    But really for a counter a text file may be the best choice.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Switzerland
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i have a datestamp on my current table, that's how i can see how many clicks are sent daily. my idea was to make just 2 table, one for today data, used only with INSERT commands and another with the archive of all clicks sent in the past, used for statistics mainly. at midnight every day, the second one would be updated with the data of the first one and the first one would be emptied.

    does that make sense?

  9. #9
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As Arpith demonstrated in his/her code, you can perform simple mathematical operations in your SQL. No need to pull values out of the database, manipulate them in your PHP then put them back into the database again. SQL *is* a language, just like PHP; and believe it or not its creators did have an understanding of mathematics!

    The problem with the way your script works is that even with an index it is still going to be inefficient. The index speeds up the SELECT query but slows down the INSERT query (because both the table and the index need to be updated).

    As a starting point, I would also go about this as Aspen has suggested. It will be more efficient to just log each redirect - either in a text file or in a database table. For the database table, just insert a record for each redirect with a timestamp:

    CREATE TABLE Redirects (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    r_date DATE NOT NULL)

    INSERT INTO Redirects SET r_date = NOW();

    Then once a day or week or whenever run a query that summarises that data for you. The following SQL will give you results set with the count for each day in the table:

    SELECT COUNT(date)
    FROM Redirects
    GROUP BY date

    Each row of table Redirects is going to be 7 bytes - so thats about (70,000 * 7) or approx half a MB of storage per day. So I would not bother to create any indexes for the table as this will only slow down your inserts (and you will ony query the table every day or week or so).

  10. #10
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Switzerland
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i just redid the code for this script using only the INSERT command on a non-indexed table and it runs REALLY faster. it used to take 1-3 seconds to redirect, now it's almost instantaneous (or whatever the english word is) thanks for all the help, i'll let you know if i have any more questions, this forum is really great

    zEBz


Bookmarks

Posting Permissions

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