Bug #64248 create secondary indexes after inserting rows statements in mysqldump
Submitted: 7 Feb 2012 10:18 Modified: 7 Feb 2012 10:28
Reporter: xiaobin lin (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:5.1, 5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump, patch, secondary indexes

[7 Feb 2012 10:18] xiaobin lin
Because there is feature of fast index creation in newest version of InnoDB, when loading data, if all the secondary indexes can be created after all rows are inserted, the performance of importing will be improved.

For the tool mysqldump, I think there can be a parameter to control the order of "insert data" and "create secondary indexes".

How to repeat:
read the code

Suggested fix:
To simplify, we can drop all the secondary indexes in the empty table, and create them after all rows are inserted.
The patch file is attached.
[7 Feb 2012 10:21] xiaobin lin
patch file for mysqldump.c

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

[7 Feb 2012 10:26] Marko Mäkelä
The patch should be against MySQL 5.5.20.
[8 Feb 2012 12:26] Sergei Golubchik
Eh. mysqldump already disables keys for inserts.
Why would anyone want to learn a new command-line switch and think about when to use it, when MySQL can do everything itself, completely automatically and transparently?

Yes, it means that InnoDB should use fast index creation for ALTER ... ENABLE KEYS.
[8 Feb 2012 12:47] Alexey Kopytov

Yes, and InnoDB should be able to disable keys in the first place.
[8 Feb 2012 13:48] Marko Mäkelä
You are right that InnoDB should support ENABLE KEYS and DISABLE KEYS in the first place. I filed a WorkLog item for precisely that quite some time ago, and linked it to this bug.

However, it is not that simple to support new data dictionary syntax in InnoDB. One thing that makes it more complex is that there is an internal data dictionary (SYS_TABLES, SYS_COLUMNS, SYS_INDEXES, SYS_FIELDS) in InnoDB. The info about disabled keys would have to be stored somewhere, maybe in some bit in a SYS_INDEXES record. This would mean another quirk for downgrades.

When InnoDB crash recovery starts rolling back incomplete transactions, it is working based on its internal data dictionary alone. It does not have access to .frm files at that point. The files may have been deleted as well. This is why the feature would need a change to the InnoDB data dictionary format.

Hypothetically speaking, even if (say) MySQL 5.7 supported ENABLE KEYS and DISABLE KEYS for InnoDB tables, it would take years before the feature benefits users in a production environment. Those users would be running 5.1+InnoDB Plugin or 5.5 now, and 5.6 maybe next year. When would the hypothetical 5.7 arrive? In 3 or 4 years from now?

Modifying mysqldump would benefit users of 5.1 InnoDB Plugin already now. (It is the first version of MySQL/InnoDB that supports index creation without copying the table row-by-row.)