title: mysql single table SELECT query ORDER BY causes FILESORT
body: <p>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.</p>
  
  <p>Here's the table definition and query:</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>Also, here's the explain result set I get:</p>
  
  <pre><code>+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+
  | 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 |
  +----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+
  </code></pre>