Re: How to avoid filesort in ORDER BY?

[irek kordirko]

Andy S Wrote:
-------------------------------------------------------
> So can MySQL run this query without filesort?

Sometimes yes, sometimes no, it is difficult to say.
It depends on many factors, mainly on a structure of the table (or tables),
fields in primary key, indexes , number of rows in the table,
physical size of the row, number of unique values in indexes (index cardinality),
the engine, on mysql version, on the query itself...
If you want someone to assist in optimizing that query, please provide output of the following statements:
SHOW TABLE STATUS like 't1'
SHOW CREATE TABLE t1;
SHOW INDEX FROM t1;
EXPLAIN EXTENDED your_query;

I can easily create a test case to show you that it can be done (see below).
But it's also easy to slightly change this test case to show the opposite.

create table t1(
 group_id int,
 f1 varchar(100),
 f2 varchar(200),
 score int
);

set @x = 0;
insert into t1( group_id, f1, f2, score )
select (@x := @x + 1) x,
       case round( rand() * 4 ) 
          when 0 then 'zero'
          when 1 then 'abc'
          when 2 then 'bcd'
          when 3 then 'efg'
          when 4 then 'gfa'
       end y,
       z,
       round( rand() * 100 )
from ( 
    select 1 from information_schema.tables a
    cross join information_schema.tables b
) xx
cross join (
select 'xyz' z union select 'abc' union select 'qwe' union select 'ggg'
) z
;

create index magic_ix on t1( group_id, f2, score ); 

analyze table t1;
;
explain extended
SELECT *
FROM t1
WHERE group_id = 123 AND (f1 = 'abc' OR f1 = 'efg') AND f2 = 'xyz'
ORDER BY score DESC LIMIT 10; 
+----+-------------+-------+------+---------------+----------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | ref  | magic_ix      | magic_ix | 208     | const,const |    1 |   100.00 | Using index condition; Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

Edited 1 time(s). Last edit at 01/27/2012 08:25AM by irek kordirko.