0

I am trying to select about 800,000 rows in 7+ millon table using this query

 SELECT * FROM news_completed USE INDEX (brand_id) WHERE `brand_id`IN (6346,6324,364,6460,6341,6495,6340,6438,6496,6439,6345,6344,6343,6497,6446,6450,6445,6443,6338,6440,6492,6449,6435,6609,6493,6347,6442,6339,6437,6444,6436,6494,6342,6575,6574,6576);

Column brand_id is indexed but index is not used, even if I force use in the query. EXPLAIN statement: http://hpics.li/209e767

But, if I reduce the number of brands within the IN to half (aprox), then the brand_id index is used: http://hpics.li/02f9822

Could you explain me what is this limitation and how I can avoid it? Or build my query in a more efficient way?

Thanks a lot, Maxime.

Maxime
  • 9
  • 3
  • I think we'd need to see proper DDLs and the EXPLAIN. – Strawberry Jan 06 '15 at 13:34
  • There is no restriction in the number of elements for the IN clause. Try to use EXPLAIN to get more information of the query. – semoru Jan 06 '15 at 13:34
  • I can't post an image with the EXPLAIN because I am new here :( Using EXPLAIN I see that no key is used in the first case, and eliminating most of the brand_ids I then see that the brand_id index is used (using EXPLAIN) – Maxime Jan 06 '15 at 20:45
  • Hi, I could post the EXPLAIN statements through links, I hope this helps. – Maxime Jan 07 '15 at 13:22
  • You should post the execution plan as **formatted** text, not as an image. –  Jan 07 '15 at 13:23
  • This is a table, I don't know how to embed it into my post that's why I uploaded images. How should I format that text? thanks. – Maxime Jan 07 '15 at 13:53
  • no one has an idea? :( – Maxime Jan 12 '15 at 13:55

1 Answers1

0

I think with a FORCE INDEX I solve my problem even though I am not sure why index is not used by default.. I guess the optimiser thinks a full scan is better than a range scan, my tests makes me think it is wrong..

MySQL not using indexes with WHERE IN clause?

Community
  • 1
  • 1
Maxime
  • 9
  • 3