-1

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.

Abdul Manaf
  • 248
  • 1
  • 8
Zenth
  • 121
  • 2

1 Answers1

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.

Gregory MOUSSAT
  • 1,737
  • 2
  • 25
  • 48