Bug #49120 mysqldump should have flag to delay creating indexes for innodb plugin releases
Submitted: 26 Nov 2009 5:28 Modified: 2 May 2011 19:47
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:5.1-innodb-plugin OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, plugin

[26 Nov 2009 5:28] Morgan Tocker
In InnoDB plugin releases (with the feature fast index creation), the recommended way to load data into an InnoDB table changes from:

- Create the table with all indexes
- Load the data


- Create the table
- Load the data
- Add the secondary indexes

I would like to request that mysqldump be updated to apply this optimization.

How to repeat:
See: http://www.innodb.com/doc/innodb_plugin-1.0/innodb-create-index.html#innodb-create-index-e...
[26 Nov 2009 7:44] Valeriy Kravchuk
Thank you for the feature request.
[2 May 2011 19:47] Morgan Tocker
This has been added to Percona Server:

"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:

 * KEY, UNIQUE KEY, and CONSTRAINT clauses are omitted from CREATE TABLE statements corresponding to InnoDB tables.
 * An additional ALTER TABLE is issued after dumping the data, in order 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"
[27 Jan 2013 16:37] Shantanu Oak
This feature should be applicable to MyISAM table type as well.

Attachment: mysqldump_create_index_last.patch (application/octet-stream, text), 4.55 KiB.