Bug #744103: Make use of InnoDB fast index creation in mysqldump, ALTER TABLE and OPTIMIZE TABLE
Initial implementation of innodb_expand_fast_index_creation.patch.
This patch expands the applicability of InnoDB fast index creation to mysqldump, ALTER TABLE and OPTIMIZE TABLE as follows:
1. mysqldump has now a new option, --innodb-optimize-keys, which 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.
This part of the patch is an implementation of the feature request reported as MySQL bug #49120.
More specifically:
- KEY, UNIQUE KEY and CONSTRAINT specifications are omitted from CREATE TABLE corresponding to InnoDB tables.
- an additional ALTER TABLE is issued after dumping the data to create the previously omitted keys.
Delaying foreign key creation does not introduce any additional risks as mysqldump always prepends its output with SET FOREIGN_KEY_CHECKS=0 anyway.
2. When ALTER TABLE requires a table copy, secondary keys are now dropped and recreated later after copying the data. The following restrictions apply:
- only non-unique keys can be involved in this optimization
- if the table contains foreign keys, or a foreign key is being added as a part of the current ALTER TABLE statement, the optimization is disabled for all keys.
This part of the patch is an implementation of the feature request reported as MySQL bug #57583.
3. As OPTIMIZE TABLE is mapped to ALTER TABLE ... ENGINE=InnoDB for InnoDB tables, it now also benefits from fast index creation with the same restrictions as for ALTER TABLE.