Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

i don't understand the problem in this query, when I make a query with a condition of category_id = 'XX' the query take MINUTES to execute, but the same query without filter by category_id take 0.09 seconds, and the category_id is an int(11) Unsigned INDEX..

Here is my tables:

http://www.youcial.es/tableproblems/table_business.htm

http://www.youcial.es/tableproblems/table_products.htm

EXPLAINS:

http://www.youcial.es/tableproblems/explain_fast.htm

http://www.youcial.es/tableproblems/explain_slow.htm

The ONLY difference is the filter by category_id.. I don't find the slow performance problem filtering by this field.. What may be the possible problem?

Thanks.

share|improve this question

closed as off topic by Sven, Ladadadada, Magellan, Ward, Scott Pack Nov 21 '12 at 14:21

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

This is because you use LIMIT 0,30
Your request without category_id = 'XX' find the first 30 values very fast.
Your request with category_id = 'XX' have to dig into your tables to find 30 rows (probably because you don't have a lot of 'xx').

If you remove LIMIT 0,30 your 2 requests will have about the same run lenght.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.