やっと理解した。
mysql> show tables; +----------------+ | Tables_in_hoge | +----------------+ | test | +----------------+ 1 row in set (0.01 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL, `value1` varchar(255) DEFAULT NULL, `value2` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `value1` (`value1`), KEY `value2` (`value2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from test order by value1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: Using filesort 1 row in set (0.00 sec) mysql> explain select * from test where value1 = 'test' order by value1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ref possible_keys: value1 key: value1 key_len: 768 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from test where value2 = 'test' order by value1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ref possible_keys: value2 key: value2 key_len: 768 ref: const rows: 1 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from test where value2 = 'test' order by value2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ref possible_keys: value2 key: value2 key_len: 768 ref: const rows: 1 Extra: Using where 1 row in set (0.01 sec)