MySQL Forums
Forum List  »  InnoDB

Index re-creation after composite primary key change
Posted by: Jakub G
Date: November 01, 2012 09:56AM

I need to extend a column in a quite big table (~90M rows). (MySQL 5.1.35, InnoDB)

It's a varchar(15) column that needs to be extended to varchar(20). This column is a part of the primary key.

I did some reading in the mysql documents and it looks like a temporary table will be created for that process and at the end a swap will be done. So it won't lock the table and things like that...

But how will be the index re-creation process behave, I assume that the initial index will be dropped and a new one has to be created?

Will it be re-created while the temporary table is populated so without impact on the performance?

I did some reading and I found an indirect proof for my theory(that everything should be ok).
On this page: http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-create-index-overview.html

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

If somebody could backup my theory...


Cheers!

Options: ReplyQuote


Subject
Views
Written By
Posted
Index re-creation after composite primary key change
1563
November 01, 2012 09:56AM


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.