MySQL Bugs: #49120: mysqldump should have flag to delay creating indexes for innodb plugin releases

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

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

to:

- 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...

Thank you for the feature request.

This has been added to Percona Server:
http://www.percona.com/docs/wiki/percona-server:features:innodb_fast_index_creation

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

This feature should be applicable to MyISAM table type as well.

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