mysql using filesort instead of index

Question

I have this query:

SELECT * FROM table WHERE x >= 500 AND x < 5000 ORDER BY date DESC LIMIT 0,50

I have index: x, date - Btree

Why is this query using index and filesort, if I have index on both values.

x= integer date = date

tyble type = myisam

explain:

ID: 1
select_type: SIMPLE
table: d
type: range
possible_keys: sort
key: sort
key_len: 2
ref: null
rows: 198
extra: using index condition; using filesort

Answer 1

The query is using filesort because it is a range query. Filesort would desappear if the query used exact equation.

But you probably know that filesort is actualy a misname and has actually no relation to files.

Answer 2

From the reference -

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

The key used to fetch the rows is not the same as the one used in the ORDER BY: SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

Try to add index INDEX (date, x).