MySQL Forums
Forum List  »  Optimizer & Parser

"id IN (x,y)" versus "id=x OR id=y" with ORDER BY
Posted by: Robert Bergs
Date: February 21, 2006 07:00AM

Given this example table:

create table robfoo2 (document_id INT, word_id INT, sequence INT);

with this index:

create index document_id_sequence ON robfoo2 (document_id, sequence);

Can anyone explain why these two differ:

mysql> explain select document_id, word_id, sequence FROM robfoo2 where document_id IN (7029617) ORDER BY document_id, sequence \G
*************************** 1. row ***************************
table: robfoo2
type: ref
possible_keys: document_id_sequence
key: document_id_sequence
key_len: 5
ref: const
rows: 62
Extra: Using where; Using filesort

AND

mysql> explain select document_id, word_id, sequence FROM robfoo2 where document_id=7029617 ORDER BY document_id, sequence \G
*************************** 1. row ***************************
table: robfoo2
type: ref
possible_keys: document_id_sequence
key: document_id_sequence
key_len: 5
ref: const
rows: 62
Extra: Using where
1 row in set (0.00 sec)

Given the index, I wouldn't have thought a filesort was necessary since MySQL should be able to use the index to help with the ORDER BY. If you put two document_id values in the IN(), it doesn't filesort but with many values in the IN() it sometimes decides to.

Can anyone tell me how MySQL makes the decision to filesort or not in this case?

Options: ReplyQuote


Subject
Views
Written By
Posted
"id IN (x,y)" versus "id=x OR id=y" with ORDER BY
2598
February 21, 2006 07:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.