Many DDL operations on tables and indexes (
DROPstatements) can be performed online. See Section 14.13, “InnoDB and Online DDL” for details.
Online DDL support for adding secondary indexes means that you can generally speed up the process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.
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.