MySQL Bugs: #64248: create secondary indexes after inserting rows statements in mysqldump

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

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

patch file for mysqldump.c

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

The patch should be against MySQL 5.5.20.

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.

Sergei,

Yes, and InnoDB should be able to disable keys in the first place.

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