One of the optimizations we have introduced in MariaDB 5.5 is Extended keys. The idea behind it is rather simple. Inside InnoDB, every secondary index has an invisible suffix of primary key columns. That is, when you create an index:
ALTER TABLE innodb_tbl ADD INDEX (column1, column2);
you’re actually creating this
ALTER TABLE innodb_tbl ADD INDEX (column1, column2, primary_key_column1, …, primary_key_columnN);
The index is extended with primary key columns.
KEYS does not show these extra key parts, but they are
Traditionally, MySQL optimizer was half-aware of these extra
columns. It knew that doing an index-only scan on InnoDB’s
secondary key would read the primary key columns also, and used
this property. On the other hand, the optimizer was not able to
use the extra columns to do a
range access, or to resolve an
clause (correction: the optimizer did take extra columns into
account when resolving ORDER BY clauses). If you had a
SELECT … FROM innodb_tbl WHERE column1=’foo’ AND ‘column2=’bar’ AND primary_key_column1=’baz’
the optimizer was only able to use two key parts for
ref access. Extended keys optimization in MariaDB 5.5 removed this kind of limitations.
MariaDB 5.5 is able to use the extra index columns for any
purpose MySQL/MariaDB can use an index for.
So, what’s the news? The news is this commit. It seems, Oracle has decided to follow and
also support extended keys. The email subject is “
into mysql-trunk“, I suppose it means that the feature has
been pushed into whatever will be the next version after MySQL
5.6 (mysql-trunk tree is not publicly available, so there’s a lot
Looking at their patch, I see two differences from MariaDB’s version:
optimizer_switchflag is named extended_keys in MariaDB and extended_secondary_keys in MySQL-trunk
- they inform the optimizer that the extended key is a UNIQUE key
The first one is trivial. The second one is a bit puzzling. It is true that primary key columns are unique, hence adding them to a secondary index makes the extended secondary index unique, too. But what is the benefit of knowing this? Index Unique-ness can only be used when you’ve got values for each of the columns. But if you’ve got values for each of extended key columns (column1, column2, primary_key_column1, …, primary_key_columnN), you’ve also got value for each of primary key columns (primary_key_column1, …, primary_key_columnN). Why would you want to use the secondary index then? The primary key is also usable, and it is shorter.
It would be nice to know what Sergey Glukhov (author of Oracle’s implementation) had in mind when he was coding this. Or, we’ll have to figure on our own, and add the extended-key-is-unique feature to MariaDB’s implementation.