filesort in MySQL due to an OR condition in WHERE clause

Question

I have this query:

SELECT * FROM article
   WHERE (state = 2 OR state = 3) AND category_id = 100
   ORDER BY mod_date DESC

And I have defined this index:

category_id_idx:
state
category_id
mod_date

When I run the query with an EXPLAIN, it returns this:

id: 1
select_type: SIMPLE
table: article
type: range
possible_keys: category_id_idx
key: category_id_idx
key_len: 3
ref: (NULL)
rows: 4
Extra: Using where; Using filesort

So... it's using a filesort. But if I change the WHERE removing the OR and letting only one field, this way:

SELECT * FROM article 
   WHERE state = 2 AND category_id = 100 
   ORDER BY mod_date DESC

Then the EXPLAIN returns NO filesort:

id: 1
select_type: SIMPLE
table: article
type: ref
possible_keys: category_id_idx
key: category_id_idx
key_len: 3
ref: const,const
rows: 3
Extra: Using where

Anyone knows why? I need that OR, is there a workaround?

Answer 1

That makes sense to me. The behavior is explained pretty well here:

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Essentially, the filesort can only be avoided when comparing all preceding key parts to constants. Since you are ordering by kay part 3, and comparing key part 2 to a constant, but comparing key part 3 (state) to 2 OR 3, it's not a constant, so MySQL needs to do an extra pass for the sort.

For example, consider a sample set of 4 index records for your table:

a) [2,100,2010-10-01]
b) [2,100,2010-10-20]
c) [3,100,2010-10-10]
d) [3,100,2010-11-01]

If you were just looking for state 2, then you would want rows b,a which are consective in the index, avoiding the filesort. If you were just looking for state 3, then you would want rows d,c which are consective in the index, avoiding the filesort.

But what you are asking for is state 2 OR 3, which means you want rows d,b,c,a which are not consecutive in the index. That's why the filesort is required.

Answer 2

There is a workaround for this but it will require mysql version 5.1

FORCE {INDEX|KEY}
      [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

see here http://dev.mysql.com/doc/refman/5.1/en/index-hints.html