Innodb primary key is special in many senses and I was always wondering how well MySQL is integrated with Innodb to take advantage of these special features.
Lets see which things work and which things do not:
I used the following simple table for tests:
|
1 2 3 4 5 6 |
CREATE TABLE `innodb` ( `id` int(10) unsigned NOT NULL, `a` int(11) default NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
“myisam” is same table created with MyISAM storage engine used to show difference:
MySQL Optimizer correctly knows Innodb tables is clustered by primary key in the sense it would not be faster to do external filesort than to do lookups in primary key order:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> explain select * from innodb order by id G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pktest type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 6 Extra: 1 row in set (0.00 sec) mysql> explain select * from myisam order by id G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: myisam type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using filesort 1 row in set (0.00 sec) |
MySQL Optimizer is also able to figure out every key also holds primary key value so primary key value can be read from index, making some queries index covered which previously was not: Notice “Using Index” difference
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> explain select id from innodb where a=3 G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: innodb type: ref possible_keys: a key: a key_len: 5 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec) mysql> explain select id from myisam where a=3 G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: myisam type: ref possible_keys: a key: a key_len: 5 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) |
MySQL However is unable to benefit from the fact each index internally has primary key as last column, so key on (a) is effectively key on (a,id) which means MySQL could skip filesort if ordering is done by primary key:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> explain select id from innodb where a=3 order by id G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: innodb type: ref possible_keys: a key: a key_len: 5 ref: const rows: 1 Extra: Using where; Using index; Using filesort 1 row in set (0.00 sec) |
Filesort should be avoided in this case which it is not. I now filed it as a
bug while I do not really think it would be fixed soon
