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

Please post the `EXPLAIN ...` output. – The Scrum Meister Dec 14 '11 at 06:25 — The Scrum Meister, Dec 14 '11 at 06:25

newtover · Accepted Answer · 2011-12-14T15:29:38.810

4

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.

edited Dec 14 '11 at 15:29

answered Dec 14 '11 at 14:58

newtover

31,286
11
84
89

The link you included does not suggest so. – ajreal Dec 14 '11 at 15:08
@arjreal, does not suggest what? – newtover Dec 14 '11 at 15:18
1

`First of all, this is Using temporary. Secondly, temporary tables may go to disk if they are too big, but EXPLAIN doesn’t show that. (If I interview you, I might ask you what “too big” means, or I might ask you the other reason temporary tables go to disk!)` – ajreal Dec 14 '11 at 15:23

score 3 · Answer 2 · answered Dec 14 '11 at 06:56

3

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).

answered Dec 14 '11 at 06:56

Devart

119,203
23
166
186

Nope, doesn't help. But thanx for explaining to me. – Jerry2 Dec 14 '11 at 07:01

mysql using filesort instead of index

2 Answers2

Linked