MySQL - Not using index for a big IN condition

Question

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.

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. — a_horse_with_no_name, 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

score 0 · Answer 1 · edited May 23 '17 at 11:50

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?

edited May 23 '17 at 11:50

Community

1
1

answered Jan 12 '15 at 16:50

Maxime

9
3

MySQL - Not using index for a big IN condition

1 Answers1