Mysql single table SELECT ORDER BY query calls FILESORT

I looked through several similar posts, trying to get information on how to override my index, but I can not figure it out. Each time I turn on the ORDER BY clause, it uses filesort to return a result set.

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 ;

In addition, a result set is obtained here:

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+
| 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 |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+
+3
source share
2 answers

Why s.isactivenot used as an index?

MySQL ( SQL, ) , . , , (My) SQL , .

- ; .

MySQL ctime?

ctime composite . MySQL , *)
(-) , MySQL filesort.
, order by isactive , department ; order by department .
order by isactive , , , isactive .

*) , 97% .

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

+3

fileort MySQL?

, , , ( , ).

:

, filesort . , , . . Filesort "". .

+1

All Articles