Re: Impact on indexes while renaming tables

[Ingo Strüwing]

Yes it is. CREATE INDEX is internally mapped to ALTER TABLE.

Every ALTER TABLE recreates the full table, including copying the data and creating all indexes. So you should make all changes with one ALTER TABLE statement. Not every change in its own statement.

My question to Manoj is: Do you create all indexes after loading the data and before renaming? The indexes of a table travel with its name.

So the best way to do this would be:

Create a new table without indexes.
Load the data into it.
Alter the table, adding all indexes you want to have on it.
Rename the old table to something.
Rename the new table to the old name.

The reason for loading the data before creating the indexes is that the index creation on existing data is mostly much faster than index creation on the fly (update every index for every row inserted). But if you have a few indexes only and not very much data, the thing might be different for you. Try it out.

BTW. We are working on improvements for ALTER TABLE. In future versions we will no longer require a full table rebuild for adding an index. For some storage engines this might come in 5.1 (I cannot guarantee this though). For MyISAM it will be a bit later.

Regards
Ingo