For DDL operations on tables and indexes (
CREATE
,ALTER
, andDROP
statements), the most significant aspect forInnoDB
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 Section 13.7.2, “Fast Index Creation in the InnoDB Storage Engine” 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, notDELETE FROM
. Foreign key constraints can make atbl_name
TRUNCATE
statement work like a regularDELETE
statement, in which case a sequence of commands likeDROP TABLE
andCREATE 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 theCREATE TABLE
statement, and plan ahead so that you do not need toALTER
orDROP
the primary key afterward.