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

Got defined indexes?

There are indexes created for 'available', 'quantity', 'id', 'category_id', 'rating', and 'stats'.

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?

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.