MySQL Forums
Forum List  »  MyISAM

Re: Option to avoid table rebuild on CREATE INDEX?
Posted by: Rick James
Date: February 04, 2015 07:25PM

(History lesson...)
MySQL started out lean and mean. CREATE/DROP INDEX mapped to ALTER because that was 'lean'. ALTER always rebuilt the table because that made the code quite lean.

Eventually the "InnoDB plugin" in 5.1 got smarter. See:
http://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-overview.html
And "Fast Index Creation" became standard for InnoDB (no syntax changes needed) in 5.5.

By that time MyISAM was becoming an unsupported orphan. It continues to look like MyISAM will not get much more attention than an occasional bug fix.

(back to your question...)
So, I recommend you switch to InnoDB and at least version 5.5. In 5.6.7 more ALTERs can be done without copying the table. 5.7 makes more improvements.

Some competitors can even let you ROLLBACK a DROP. But you can't use ROLLBACK until you get to InnoDB.

REPAIR TABLE won't be needed on your 10GB table once it is in InnoDB. (However, it will be 20-30GB.)

When switching to InnoDB, check on the few things that could bite you:
mysql.rjweb.org/doc.php/myisam2innodb
But otherwise, InnoDB is the way to go; MyISAM is passé.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Option to avoid table rebuild on CREATE INDEX?
1488
February 04, 2015 07:25PM


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.