SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Trouble optimizing this query

    Hi, I am having trouble with a query. The query works like it is supposed to, but when I run it, it will scan every record in the table to get back the results, which isn't too good for performance if it's a table with a lot of records.

    I am trying to come up with a query that returns the products that has received the most clicks (Top products). I want the query to return the top 5 most popular products. This is what I have so far:

    Code:
    SELECT pid, total_views
    FROM tblproducts
    ORDER BY total_views DESC
    Limit 5
    Any help is appreciated, thanks

  2. #2
    SitePoint Enthusiast shn's Avatar
    Join Date
    Jan 2006
    Location
    Munich DE
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would you determine, which entry has most views if you didn't know them all?
    Patrick

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats what i'd like to know

  4. #4
    SitePoint Enthusiast shn's Avatar
    Join Date
    Jan 2006
    Location
    Munich DE
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hehe, that was a rethorical question

    Let me put it another way:
    You have a basket full of apples. Your goal is to sort those 5 out, that has most worms in them. Could you accomplish that task without checking each and every apple for its worms?

    I hope this makes it clear
    Patrick

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by shn
    Could you accomplish that task without checking each and every apple for its worms?
    yes, just consult the apple worm list

    that's a neat analogy, eh? if there's a list which shows how many worms are in each apple, and that list was in sequence by the number of worms, then you wouldn't have to look at the apples at all

    that's how a database index works too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast shn's Avatar
    Join Date
    Jan 2006
    Location
    Munich DE
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I absolutely agree with you. Though, normally you wouldn't create an index over total views.
    Patrick

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "index over total views"?

    maybe i would, if i knew what you meant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast shn's Avatar
    Join Date
    Jan 2006
    Location
    Munich DE
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hehe, English is not my native language

    I meant "index on the column `total_views`" - I thought this might be slow, because views need to be updated often, and so does its index. Correct me, if i'm wrong there.
    Last edited by shn; Feb 15, 2006 at 11:11. Reason: spelling and grammar
    Patrick

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by shn
    I meant "index on the column `total_views`" - I thought this might be slow, because views need to be updated often, and so does its index. Correct me, if i'm wrong there.
    no, you are indeed correct, the index would be updated every time the total_views column is updated

    however, what if the total_views column for the entire table is produced by INSERT INTO ... SELECT COUNT(...) GROUP BY pid

    if it is updated each time the product is viewed, using SET total_views=total_views+1, then i would also question whether the index is worth having
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think i'll just cache the query result. I guess there are some queries you can't do much to optimize. Thanks for the suggestions. Nice analogy by the way


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
  •