Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.