MySQL Bugs: #62025: InnoDB indexes not used properly

Bug #62025	InnoDB indexes not used properly
Submitted:	29 Jul 2011 12:57	Modified:	30 Jan 2013 15:43
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	3.23-5.6	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
When secondary index is defined on an InnoDB table, PK values are appended to the key structure, so e.g. with a table (a,b,c,d) that has (a,b) PK:

(c) key becomes (c,a,b)
(c,d) becomes (c,d,a,b)
(b) becomes (b,a)
(b,c) becomes (b,c,a)
etc

Unfortunately MySQL optimizer does not use the extended key definition for optimizer - neither for WHERE nor ORDER nor anything else, yet the cost is there - index is maintained in the extended form.

There is glue inside InnoDB - it uses index->n_user_defined_cols value to expose only index fields that MySQL expects, hiding the real index structure.

How to repeat:
CREATE TABLE innodb_table (
a INT,
b INT,
c INT,
d INT, 
PRIMARY KEY (a,b),
KEY (c)
 ) ENGINE=InnoDB

Suggested fix:
It should be opposite - instead of cheating and not exposing extended index structure to MySQL, InnoDB should actively push different index structure that it creates, so that MySQL would have full information about what kind of index it has in underlying storage engine, and how to use it properly.

Thank you for the feature request.

Feature is implemented, see wl#6266

Noted in 5.6.9 changelog.

InnoDB automatically extends each secondary index by appending the
primary key columns to it. Previously, the optimizer did not take
into account the primary key columns of the extended secondary index
when determining how and whether to use that index. Now the optimizer
takes the primary key columns into account, which can result in more
efficient query execution plans and better performance.
    
The optimizer can use extended secondary keys for ref, range, and
index_merge index access, for loose index scans, for join and sorting
optimization, and for MIN()/MAX() optimization.
    
The new use_index_extensions flag of the optimizer_switch system
variable permits control over whether the optimizer takes the primary
key columns into account when determining how to use an InnoDB 
table's secondary indexes. By default, use_index_extensions is
enabled. If you find that a query has worse performance than
previously, disable use_index_extensions to prevent the optimizer
from extending secondary indexes with primary key columns:

SET optimizer_switch = 'use_index_extensions=off';