Navigation

Percona has implemented several changes related to MySQL‘s fast index creation feature. This feature extends the ALTERTABLE command by adding a new clause that provides online index renaming capability, that is renaming indexes without rebuilding the whole table.

Fast index creation was implemented in MySQL as a way to speed up the process of adding or dropping indexes on tables with many rows. However, cases have been found in which fast index creation creates an inconsistency between MySQL and InnoDB data dictionaries.

This feature implements a session variable that enables extended fast index creation. Besides optimizing DDL directly, expand_fast_index_creation may also optimize index access for subsequent DML statements because using it results in much less fragmented indexes.

A new option, --innodb-optimize-keys, was implemented in mysqldump. It changes the way InnoDB tables are dumped, so that secondary and foreign keys are created after loading the data, thus taking advantage of fast index creation. More specifically:

InnoDB fast index creation uses temporary files in tmpdir for all indexes being created. So make sure you have enough tmpdir space when using expand_fast_index_creation. It is a session variable, so you can temporarily switch it off if you are short on tmpdir space and/or don’t want this optimization to be used for a specific table.

There’s also a number of cases when this optimization is not applicable:

UNIQUE indexes in ALTERTABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table;

ALTERTABLE and OPTIMIZETABLE always process tables containing foreign keys as if expand_fast_index_creation is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint;

mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding them back with a separate ALTERTABLE after restoring the data from a dump would actually make the restore slower;

mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed, so it is impossible to temporarily drop the corresponding index;

mysqldump –innodb-optimize-keys ignores the first UNIQUE index on non-nullable columns when the table has no PRIMARYKEY defined, because in this case InnoDB picks such an index as the clustered one.

This documentation is developed in Launchpad as part of the Percona Server source code.
If you spotted innacuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.