0

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 ?

5
  • 2
    Include order in your index. – Mark Sinkinson May 19 '14 at 15:08
  • @MarkSinkinson I don't want to include it because I have some queries without order column so this index looks sufficient for me. – Can Geliş May 19 '14 at 15:11
  • 2
    Also, rename the order column. ORDER is a SQL reserved word and should not be used for any object names – Philᵀᴹ May 19 '14 at 15:13
  • 3
    Follow Mark's suggestion. An index on (user_id, exam_id, order) is better for this query and it will still be used by queries that do not use order but only user_id and exam_id. – ypercubeᵀᴹ May 19 '14 at 15:24
  • @MarkSinkinson @ybercube I just researched a little bit about this. Yeah you're right. I'll include order to my index. Thank you. – Can Geliş May 19 '14 at 15:27
1

MySQL will chose the index based on few factors. One of them is the cardinality of the indexed field, and other one in the number of rows to be scanned (on average). In your first example, MySQL decided that it will read less rows if it is used the index on "answer_id". If you provides the stats of your table, it will be much clearer. Example of data snapshot when this happens:

user_id answer_id order
48      1         15   
48      2         17
48      3         19
48      4         13

Here, if MySQL will see that user (User_id, answer_id) will scan 4 rows, while using (exam_id) will scan only 1 row.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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