CODEWITHSUNDEEP
mysqlsortingsql-order-byfilesort
snucky
snucky

Reputation: 179

mysql single table SELECT query ORDER BY causes FILESORT

I looked through multiple similar posts trying to get input on how to redefine my index but can't figure this out. Every time i include the ORDER BY statement, it uses filesort to return the resultset.

Here's the table definition and query:

SELECT
    `s`.`title`,
    `s`.`price`,
    `s`.`price_sale`
  FROM `style` `s`
 WHERE `s`.`isactive`=1 AND `s`.`department`='women' 
  ORDER
     BY `s`.`ctime` DESC



CREATE TABLE IF NOT EXISTS `style` (
    `id` mediumint(6) unsigned NOT NULL auto_increment,
    `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP,
    `department` char(5)  NOT NULL,
    `isactive` tinyint(1) unsigned NOT NULL,
    `price` float(8,2) unsigned NOT NULL,
    `price_sale` float(8,2) unsigned NOT NULL,
    `title` varchar(200) NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `idx_grid_default` (`isactive`,`department`,`ctime`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=47 ;

Also, here's the explain result set I get:

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra                       |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | s     | ref  | idx_grid      | idx_grid | 6       | const,const |    3 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+

Upvotes: 3

Views: 1273

Answers (2)

regilero
regilero

Reputation: 30496

What does Using filesort mean in MySQL?

It does not mean you have a temporary file, it just mean a sort is done (bad name, ignore the 4 first letters).

from Baron Schwartz:

The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart.

Upvotes: 1

Johan
Johan

Reputation: 76617

Why does s.isactive not get used as an index?

MySQL (or any SQL for that matter) will not use a key if it has low cardinality. In plain English, if many rows share the same value for a key, (My)SQL will not use the index, but just real the table instead.

A boolean field almost never gets picked as an index because of this; too many rows share the same value.

Why does MySQL not use the index on ctime?

ctime is included in a multi-field or composite index. MySQL will only use a composite index if you use all of it or a left-most part of it *)
If you sort on the middle or rightmost field(s) of a composite index, MySQL cannot use the index and will have to resort to filesort.
So a order by isactive , department will use an index;
order by department will not.
order by isactive will also not use an index, but that's because the cardinality of the boolean field isactive is too low.

*) there are some exceptions, but this covers 97% of cases.

Links:
Cardinality wikipedia: http://en.wikipedia.org/wiki/Cardinality_%28data_modeling%29
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Upvotes: 3

Related Questions