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
Triage: Needs Triage: D5 (Feature request)

[29 Jul 2011 12:57] Domas Mituzas
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.
[30 Jul 2011 13:54] Valeriy Kravchuk
Thank you for the feature request.
[30 Jan 2013 8:22] Sergei Glukhov
Feature is implemented, see wl#6266
[30 Jan 2013 15:43] Paul Dubois
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';