MySQL Bugs: #20038: ALTER TABLE copies original data even when exclusive lock is issued

Bug #20038	ALTER TABLE copies original data even when exclusive lock is issued
Submitted:	24 May 2006 9:34	Modified:	27 Sep 2008 11:09
Reporter:	Francisco Reinaldo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	4.0.22	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
ALTER TABLE, i.e. ALTER TABLE ... ADD INDEX ... creates a copy of the original data to add the index.

How to repeat:
Create database.
Create table.
Execute an ALTER command on that table.

Suggested fix:
I unrestand that this is done to allow users to access the data while the index is created but this strategy is a big problem when importing huge amount of data.

When importing data (from another table or external file), it is common to remove indices prior to import data, them import the data and add indices again. Since mysql copies the orginal data, it takes a long time to create an index, furthermore, this is done for each index even when multiple indices are issued in the same ALTER TABLE statement.

I propose two enhancements:

a) ALTER TABLE should reuse the same temporary data while creating multiple indices.

b) If an exclusive lock is issued, the original data should not be copied since no other connection can access the data anyway.

For more information take a look at thread:

https://support.mysql.com/9763

I believe this bug should be fixed in 4.0 because it is not a trivial problem and I don't think is a good idea to force users to update to 4.1 or 5.0 since the database formats are not compatible.

This defect of creating a copy of the files is not only a performance issue but also creates unstable code since a lengthy process can fail at the end, that is, at the index creation because lack of disk space or any other I/O issue.

It happened to us in several occasions that the data and index disappeared without even notifying the connected client which was performing the ALTER INDEX operation that something went wrong.

Modifying the code so the index is created before inserting the data is not an option since it impacts performance. In own tests, it impacted performance 2 or 4 times.

This issue also affects people importing huge external files with millions of records. For a 1Gb file for instance, it can create a 3GB index and therefore you will need an addition 1Gb of additional space only to copy the data and cross your finger that the process that not crash when executing the ALTER INDEX  instruction.

I hope my comments serve to reconsider mysql's strategy about when the bug should be fixed.

Thank you for a reasonable feature request.

This is fixed in 5.1 with fast add/drop index.