Description:
MySQL 5.6.10 appears to have a hardcoded check that the engine is InnoDB when it determines whether secondary keys contain the primary key. This makes it impossible for other engines that have tables with the same properties to be treated the same way as InnoDB.
The check is in sql/table.cc, function open_binary_frm(..), line #1104:
use_extended_sk= (legacy_db_type == DB_TYPE_INNODB);
The consequence is that optimizations that use extended secondary keys (e.g. index_merge in the repro) are disabled for all engines but InnoDB.
The repro case uses ClouSE -- the Cloud Storage Engine for MySQL, but you should be able to reproduce it with any storage engine that is not InnoDB.
index_merge works correctly in MySQL 5.5.
How to repeat:
*** repro:
drop table if exists clse_unit_test;
create table clse_unit_test ( id int key, id_1 int not null, id_2 varchar( 16 ) not null, key( id_1 ), key( id_2 ) ) engine = 'clouse';
start transaction;
insert into clse_unit_test values ( 256 * 1 + 6, 77, 'foo' );
insert into clse_unit_test values ( 256 * 2 + 5, 42, 'bar' );
insert into clse_unit_test values ( 256 * 3 + 4, 33, 'foo' );
insert into clse_unit_test values ( 256 * 4 + 3, 77, 'foo' );
insert into clse_unit_test values ( 256 * 5 + 2, 33, 'baz' );
insert into clse_unit_test values ( 256 * 6 + 1, 42, 'foo' );
commit;
explain select * from clse_unit_test where id_1 = 42 and id_2 = 'foo';
*** result:
1 SIMPLE clse_unit_test ref id_1,id_2 id_1 4 const 2 Using where
*** expected:
1 SIMPLE clse_unit_test index_merge id_1,id_2 id_1,id_2 4,18 NULL 1 Using intersect(id_1,id_2); Using where; Using index
Suggested fix:
Instead of checking if the engine is InnoDB, MySQL should check if the engine has a certain capability (so that any engine can specify it). Checking for HA_PRIMARY_KEY_IN_READ_INDEX seems like a good condition (after all that what it means -- all indexes include the primary key).
If you feel like the new optimization logic is not compatible with HA_PRIMARY_KEY_IN_READ_INDEX capability, you can add a new one so that engines can opt in.
