MySQL Forums
Forum List  »  MyISAM

Option to avoid table rebuild on CREATE INDEX?
Posted by: David Lambert
Date: February 03, 2015 05:39PM

Today I noticed a limitation in the MyISAM storage engine: adding an index (using CREATE INDEX, which is mapped to ALTER TABLE) forces a rebuild of the underlying data (.MYD) file, as if I issued an OPTIMIZE TABLE statement... even if I don't want this behavior!

So suppose I have a cluster with one database, one large table, say 10GB in the MYD file and 500M in the MYI file. I only needed 11GB disk space for that cluster. However, if I decide I want another index on another column of the dataset, which index might take another 500M, I temporarily need up to 20GB of disk space while the .MYD file is rebuilt.

Is there any fundamental reason that the MYD file has to be rewritten when an index is added or dropped?

I would suggest a feature of an alternate mode for adding/dropping indices to conserve disk space at the expense of preserving fragmentation, maybe ALTER TABLE NO OPTIMIZE. (Or is there such a feature in a newer version than 5.5)? The .MYD file would be left untouched, the .MYI file would be reused with any new index added at the end, or if an index was dropped, the file would be truncated to release free pages at the end but otherwise left in place.

Does MySQL have any other storage engines that can create an index without rewriting the base data?

Do any competitor databases have this behavior where they rewrite the base data when creating or dropping an index?

Options: ReplyQuote

Written By
Option to avoid table rebuild on CREATE INDEX?
February 03, 2015 05:39PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.