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 |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+
source
share