MySQL is using wrong index, I think

Question

This is my table:

- user_id INT (foreign key on `user` with the reference `id`)
- question_id INT (foreign key on `question` with the reference `id`)
- option_id INT (foreign key on `option` with the reference `id`)
- exam_id INT (foreign key on `exam` with the reference `id`)
- order INT

My Index is: user_id, exam_id

My Query:

select * from `user_answer` where `user_id` = '48' and `exam_id` = '1' and `order` > '10' order by `order` desc limit 1;

I think it should use my index but this is the result of EXPLAIN:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'user_answer', 'range', 'user_answer_exam_id_foreign,user_answer_user_id_exam_id_index', 'user_answer_exam_id_foreign', '4', NULL, '10', 'Using where; Using filesort'

Apperently, It is not using my index. When I use FORCE INDEX:

select * from `user_answer` force index (user_answer_user_id_exam_id_index) where `user_id` = '48' and `exam_id` = '1' and `order` > '10' order by `order` desc limit 1;

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'user_answer', 'ref', 'user_answer_user_id_exam_id_index', 'user_answer_user_id_exam_id_index', '8', 'const,const', '10', 'Using where; Using filesort'

Any idea what's wrong with me or MySQL ?

Try not putting quotes around your integer literals? Also, can you explain a little bit about the data? Is order a unique value in the table? At present the combination of order > 10 ORDER BY order DESC LIMIT 1 means that the optimiser believes that sorting by order first will be more beneficial than filtering by user_id, exam_id. — MatBailie
– MatBailie, Commented May 19, 2014 at 15:08
I am not sure how this affects the result, but just tried and EXPLAIN result is same. — Can Geliş
– Can Geliş, Commented May 19, 2014 at 15:10
also for your query its better to use combined index as (user_id,exam_id,order) and have the query follow where condition from left to right as you have now. — Abhik Chakraborty
– Abhik Chakraborty, Commented May 19, 2014 at 15:10
How many rows on the table, and how many have a user_id of 48 and how many an exam_id of 1? — Kickstart
– Kickstart, Commented May 19, 2014 at 15:12
Other queries without order would still use the index with order as the last field. — Kickstart
– Kickstart, Commented May 19, 2014 at 15:13

Kickstart · Accepted Answer · 2014-05-19 15:31:53Z

1

MySQL will ignore indexes which do not narrow the results down by much.

As a very rough rule of thumb if an index doesn't reduce the rows below about 1/3 then it is ignored (this is very rough, but gives you an idea). Ie, if you have 100 records and the index only narrows it down to ~40 then MySQL will likely ignore the index. It is faster for it to check each row individually than it is for it to use the index.

Annoyingly even when it choses not to use an otherwise valid index, it still reports the query on the slow query log for now using an index!

answered May 19, 2014 at 15:31

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Can Geliş Over a year ago

Thanks! I didn't know that row count in the table affects the indexes used.

Collectives™ on Stack Overflow

MySQL is using wrong index, I think

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related