Skip navigation links

MySQL Forums :: InnoDB :: Index re-creation after composite primary key change


Advanced Search

Re: Index re-creation after composite primary key change
Posted by: Rick James ()
Date: November 02, 2012 08:29PM

> So it won't lock the table and things like that...
False. The recreate-and-swap is for safety.
During the recreate, all the indexes will be rebuilt.
It _may_ be the case that you can continue to _read_ the table during the ALTER. (I don't know.) Writes are definitely locked out.

> without impact on the performance?
False. There will be a lot of disk I/O. This may or may not impact other activity.

> "The CREATE INDEX and DROP INDEX commands work by creating a new, empty table defined with the requested set of indexes. It then copies the existing rows to the new table one-by-one, updating the indexes as it goes."
True. And it is especially important for the PRIMARY KEY, which is embedded with the data.

One way to look at InnoDB structure is:
* The data is in a BTree arranged by the PRIMARY KEY. The leaf records contain the entire row.
* Each secondary index (if any) is in its own BTree, arranged by its key. The leaf records contain the PRIMARY KEY fields. (That way, it can go the the first BTree to find the entire row.)

MySQL does ALTER in a simple-minded way (until 5.6.7) -- recreate, copy over, swap. A _few_ special cases have been implemented. (For example, adding a value to an ENUM -- no data changes, only the schema changes.)

Options: ReplyQuote


Subject Views Written By Posted
Index re-creation after composite primary key change 797 Jakub G 11/01/2012 09:56AM
Re: Index re-creation after composite primary key change 600 Rick James 11/02/2012 08:29PM
Re: Index re-creation after composite primary key change 519 Jakub G 11/03/2012 07:45AM


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.