Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts

    No results vastly slower than some results.

    After numerous tests on a database table with nearly 200,000 records I have found that a query the returns records is nearly 500 times faster than one that returns no records.

    For instance, if the following query is run it will return no results and require 2.5 seconds to complete:
    Code:
    SELECT items.*, prices.price
    FROM items, prices
    WHERE items.available = 'Y'
    AND items.quantity >0
    AND prices.id = items.id
    AND prices.price >15
    AND items.category_id =4987
    ORDER BY items.rating DESC , items.stats DESC
    LIMIT 10
    If the following query is executed it will return 10 results and require 0.005 seconds to complete:
    Code:
    SELECT items.*, prices.price
    FROM items, prices
    WHERE items.available = 'Y'
    AND items.quantity >0
    AND prices.id = items.id
    AND prices.price >15
    AND items.category_id =8754
    ORDER BY items.rating DESC , items.stats DESC
    LIMIT 10
    The only difference is there are no items in category 4987 matching the criteria but there are in 8754.

    Is there any way I can get around this bottleneck? If enough visitors hit a query like this at the same time the entire site slows to a crawl while the database is overloaded with the search.

    I have tried these out on a server running MySQL 4.1 and on another running 5.0 with the same result ratios.

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Got defined indexes?

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    There are indexes created for 'available', 'quantity', 'id', 'category_id', 'rating', and 'stats'.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Try rearranging your WHERE clause to specify category_id first. This may not help in MySQL, but in DB2 this can make a difference.

    Also, have you done an explain on the query? What does it show?

  • #5
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I just realized a major goof up when I posted the query examples. I just looked back at the second query in my logs and is says "category_id2" which does NOT have an index. I stripped off the #2 and performed the test again and the time to execute is the same regardless of what is returned! I am now building indexes to utilitize the other category_id fields and am hoping this was the only real issue.

    I will also try rearranging the WHERE clause as suggested just to see if I can squeeze out a little more speed, though I think MySQL already optimizes the query structure on its own.


  •  

    Posting Permissions

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