Description:
i have a table with following structure:
CREATE TABLE `test_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fid` int(11) NOT NULL,
`content` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `un_fid_con` (`fid`,`content`)
) ENGINE=InnoDB;
lets say, i put some data in the table like:
insert into test_innodb values(2, 1, 'test1'),(4, 2,'test2'), (6, 6, 'test3');
now, when i do an explain on a select on fid with order by id, innodb is using id as the index, whereas myisam would use fid.
mysql> explain select * from test_conents where fid=1 order by id;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | test_innodb | index | fid | PRIMARY | 4 | NULL | 3 | Using where; Using index |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+--------------------------+
now change the engine to myisam.
mysql> alter table test_innodb engine=myisam; rename table test_innodb to test_myisam;
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from test_myisam where fid=1 order by id;
+----+-------------+------------------------+------+---------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+------+---------------+-----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | test_myisam | ref | fid | fid | 4 | const | 1 | Using where; Using filesort |
+----+-------------+------------------------+------+---------------+-----------+---------+-------+------+-----------------------------+
1 row in set (0.01 sec)
isn't this wrong on part of innodb to be using an index which is not in where clause, but is only in order by.
How to repeat:
CREATE TABLE `test_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fid` int(11) NOT NULL,
`content` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `un_fid_con` (`fid`,`content`)
) ENGINE=InnoDB;
insert into test_innodb values(2, 1, 'test1'),(4, 2,'test2'), (6, 6, 'test3');
explain select * from test_conents where fid=1 order by id;
alter table test_innodb engine=myisam;
rename table test_innodb to test_myisam;
explain select * from test_myisam where fid=1 order by id;
