MYSQL using filesort when indexing an ORDER BY column in a simple query

Question

I have seen many similar questions, but I do not see a solution that will work for me. Or maybe I'm just tight. :) Hope someone can help me.

I have the following table:

CREATE TABLE `table_name` ( `value1` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `value2` varchar(50) NOT NULL, `value3` tinytext, `value4` tinytext, `value5` tinytext, `value6` char(3) DEFAULT NULL, `value7` char(3) DEFAULT NULL, PRIMARY KEY (`value1`), KEY value2_index ('value2') ) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=latin1; 

To verify that my indexes are set, this is the result of SHOW INDEX:

 +--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | table_name | 0 | PRIMARY | 1 | value1 | A | 43 | NULL | NULL | | BTREE | | | table_name | 1 | value2_index | 1 | value2 | A | NULL | NULL | NULL | YES | BTREE | | +--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) 

Where I run this query:

 SELECT value2, value6 FROM table_name WHERE value7 = 'Yes' ORDER BY value2; 

I thought adding an index to value2 would stop the request to use filesort. However, EXPLAIN shows differently:

 +----+-------------+--------------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | table_name | ALL | NULL | NULL | NULL | NULL | 43 | Using where; Using filesort | +----+-------------+--------------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) 

What am I doing wrong?

Thanks!

Answer 1

First, the query should find rows with the value 7 = 'Yes', that is, your index should include the value 7 as the first column to be used. For those rows that match, it should be ordered by value2. Thus, a query needs an index with several columns (value7, value2).

Learn more about indexes in MySQL docs .