Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Optimizing InnoDB DDL Operations

8.5.7 Optimizing InnoDB DDL Operations

  • For DDL operations on tables and indexes (CREATE, ALTER, and DROP statements), the most significant aspect for InnoDB tables is that creating and dropping secondary indexes is much faster in MySQL 5.5 and higher, than in MySQL 5.1 and before. See InnoDB Fast Index Creation for details.

  • Fast index creation makes it faster in some cases to drop an index before loading data into a table, then re-create the index after loading the data.

  • Use TRUNCATE TABLE to empty a table, not DELETE FROM tbl_name. Foreign key constraints can make a TRUNCATE statement work like a regular DELETE statement, in which case a sequence of commands like DROP TABLE and CREATE TABLE might be fastest.

  • Because the primary key is integral to the storage layout of each InnoDB table, and changing the definition of the primary key involves reorganizing the whole table, always set up the primary key as part of the CREATE TABLE statement, and plan ahead so that you do not need to ALTER or DROP the primary key afterward.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.