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


Advanced Search

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 1467 Jakub G 11/01/2012 09:56AM
Re: Index re-creation after composite primary key change 1084 Rick James 11/02/2012 08:29PM
Re: Index re-creation after composite primary key change 885 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.