What are the performance implications of creating an index in MySQL?

Question

The MySQL reference guide doesn't (from what I see) detail the implications of creating an index; by this I mean the performance implications and any locks it may take on the table or columns against which is building the index.

If anyone could give me an idea as to what the implications are, it would be appreciated; what would be appreciated even more is a link to some documentation where I can answer this question for myself!

Regards,

Chris :D

Answer 1

Since the creation of an index is DDL, it must always incur a lock on all moving parts of the table. How does the locking work ?

MyISAM

For the table mydb.mytable whose datadir is /var/lib/mysql, there are file handles on the following files:

• /var/lib/mysql/mydb/mytable.frm (Table Definition)
• /var/lib/mysql/mydb/mytable.MYD (MyISAM Data)
• /var/lib/mysql/mydb/mytable.MYI (MyISAM Indexes)

By means of a full table lock, all INSERTs, UPDATEs, and DELETEs are held in abeyance until the completion of the index creation.

InnoDB

For the table mydb.mytable whose datadir is /var/lib/mysql, there are file handles on the following files:

• innodb_file_per_table disabled
  • /var/lib/mysql/mydb/mytable.frm (Table Definition)
  • /var/lib/mysql/ibdata1 (Data and Index Pages for mytable)
• innodb_file_per_table enabled
  • /var/lib/mysql/mydb/mytable.frm (Table Definition)
  • /var/lib/mysql/mydb/mytable.ibd (InnoDB Data and Index Pages)

By means of a full table lock as well as the Clustered Index, all INSERTs, UPDATEs, and DELETEs are held in abeyance until the completion of the index creation. There are also too many moving parts to the lifecycle

Can I setup a temp table for index creation?

One could attempt to use an external table as follows:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ADD INDEX (some_column);
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;
DROP TABLE mydb.mytable_old;

This does not work under two conditions

• CONDITION #1 : If there are any INSERTs, UPDATEs, or DELETEs during the first 3 steps, the RENAME steps will have the incoming data disappear.
• CONDITION #2 : If the table has foreign key constraints, CREATE TABLE mydb.mytable_new LIKE mydb.mytable; will not create those constraints in the temp table because
  • a table with foreign key constraints can only have one parent at a time
  • two tables cannot have identical constraints with the same parent

While there is a tool that can bypass these limitations and allow INSERTs, UPDATEs, or DELETEs (called pt-online-schema-change), the simplest approach for a creating an index is to just bite the bullet and let mysqld lock the table.