Bug #68469 Ext. secondary keys don't work for custom storage engines
Submitted: 23 Feb 2013 3:04 Modified: 19 Jun 2013 17:40
Reporter: Artem Livshits Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Storage Engine API Severity:S3 (Non-critical)
Version:5.6.10 OS:Any
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: custom storage engine, extended secondary key, optimization
Triage: Needs Triage: D3 (Medium)

[23 Feb 2013 3:04] Artem Livshits
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' );

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.
[23 Feb 2013 11:55] Sveta Smirnova
Thank you for the report.

Verified as described.
[19 Jun 2013 17:40] Paul Dubois
Noted in 5.6.13, 5.7.2 changelogs.

Optimizations that used extended secondary keys worked only for
InnoDB, even for storage engines with the requisite underlying