MySQL Forums
Forum List  »  Optimizer & Parser

Re: How to avoid filesort in ORDER BY?
Posted by: irek kordirko
Date: January 27, 2012 08:20AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3318
January 26, 2012 10:58PM
Re: How to avoid filesort in ORDER BY?
1505
January 27, 2012 08:20AM


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.