MySQL ALTER TABLE

Here is something interesting that causes a lot of confusion overtime while changing the structure of a database table or adding index. Problem arises when a query is extremely slow due to insufficient or non-existent indexes. The table is heavily used and contains millions of rows. Site has heavy traffic. Adding a required index is only the solution. But a common misconception is during ALTER TABLE to add an index all the selects and writes will be blocked. This is not true. During alter table only writes are blocked for the entire time but selects are allowed for most part of ALTER TABLE except during the RENAME part. Here is the detail of how ALTER TABLE works :

- open and lock the table
- create a copy of the table
- add index to the copied table
- rename the copied table to new table
- drop the old table

During the rename part there is an upgradation to name lock. It waits for all the selects to finish and then renames it. Particular problems are seen when one expects the ALTER TABLE to finish in less than a second but it is taking a lot longer. Seeing at the processlist will show that there are selects going on from the table. The processlist will not show any sign of lock or something but will be stuck at renaming table for the ALTER TABLE query. All the write queries on the table will be in lock state though.

This can be very useful in maintanence of high traffic site in devising the time for updating indexes etc.