How to Create an Index on a Large MySQL Production Table Without Table Locking

Problem Background:

Creating an index on a large MySQL table can be a daunting task, especially in a production environment where uninterrupted access is crucial. Traditional CREATE INDEX statements can result in a complete table lock, blocking all concurrent operations.

MySQL Version Considerations:

• In MySQL 5.6 and higher, index updates are performed online, allowing read and write operations to continue during index creation.
• However, in MySQL 5.5 and earlier, including InnoDB and MyISAM tables, index updates will block writes to the table.

Circular Masters Approach:

For MySQL versions prior to 5.6, one effective approach is the circular masters technique:

1. Set up a secondary master (Master B) replicating from the primary master (Master A).
2. Perform the schema update on Master B (allowing it to fall behind during the upgrade).
3. Ensure that the schema change is compatible with replicating commands from the downversion schema on Master A.
4. Atomically switch all clients from Master A to Master B.
5. Update the schema on Master A and make it the secondary master.

Percona's pt-online-schema-change Tool:

This tool automates the circular masters approach by:

• Creating a new table with the updated schema.
• Keeping the new table in sync with the original table using a trigger.
• Copying rows in batches from the original table.
• Replacing the original table with the new table.

AWS RDS Considerations:

For MySQL databases hosted on Amazon's RDS, the "Read Replica Promotion" feature can be used to facilitate schema changes without table locking. This involves making changes on a read-only slave and then promoting it to become the new master.

Alternative Techniques:

• Use a temporary table: Create a temporary table with the new index, insert data from the original table, and replace the original table with the temporary table.
• Partition the table: Split the table into smaller partitions, create the index on each partition separately, and then merge the partitions back together.
• Use a background process: Create a separate background process that gradually creates the index while the table remains accessible for normal operations. This approach is not supported in all MySQL versions.