For DDL operations on tables and indexes (
DROPstatements), the most significant aspect for
InnoDBtables is that creating and dropping secondary indexes is much faster in MySQL 5.5 and higher, than in MySQL 5.1 and before. See Section 14.13.1, “Overview of Online DDL” 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.
TRUNCATE TABLEto empty a table, not
DELETE FROM. Foreign key constraints can make a
TRUNCATEstatement work like a regular
DELETEstatement, in which case a sequence of commands like
CREATE TABLEmight be fastest.
Because the primary key is integral to the storage layout of each
InnoDBtable, and changing the definition of the primary key involves reorganizing the whole table, always set up the primary key as part of the
CREATE TABLEstatement, and plan ahead so that you do not need to
DROPthe primary key afterward.