MySQL – Creating an index in table DDL statement or separately

indexMySQL

I'm making the leap from SQL Server to MySQL and I'm trying to understand what the difference would be creating an index as part of the DDL table create statement vs creating a separate DDL statement for the each index you want to create.

The MySQL documentation on create index states

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 13.1.18, “CREATE TABLE Syntax”. This guideline is especially important for InnoDB tables, where the primary key determines the physical layout of rows in the data file. CREATE INDEX enables you to add indexes to existing tables.

Why is it important for InnoDB table to have the index created as part of the create table DDL? (Update with more details below)

What is the potential issues with an index being created after the table is created?

Update

My question was towards the statement on the MySQL's site about creating the index during the table creation vs creating it after creating the table but before filling it with data. The statement on MySQL's site makes it seem like there is a benefit, performance or otherwise, when using InnoDB tables that declaring the indexes within the DDL statement for the table is best practice but they don't explain why.

Referencing how I would approach this in SQL Server, partly because of DDL limitations and partly because I like the explicit nature, is that I would make my DDL statement and assign the primary key along with any foreign key constraints. After that I would assign my clustered index, if it it wasn't the primary key, and secondary indexes (unique and non unique). But in either approach if I was to create my clustered index with the table DDL or separate the end result would be the same. Their statement is making it sound like the RDMS will handle the table creation differently depending on the approach even though the result in the end is the same, a primary key and a clustered index.

Best Answer

In InnoDB, the PRIMARY KEY is, by definition, clusered and BTree-organized. If you populate a table before specifying the desired PK, a hidden PK is generated. This hidden PK must be removed and replaced by the desired PK and the table must be re-sorted according to the new PK. No "harm" is done, but extra time is taken and the BTree may become more fragmented.

Adding secondary keys (any key other than the PK) separately from the CREATE TABLE may or may not be inefficient. But, again, no "harm" is done. The inefficiency depends on the version, the nature of the index, etc.

FOREIGN KEYs are another matter -- they must be applied in the "right order". This can be achieved by having the CREATE TABLEs in the "right order" or by separately adding the FKs in the "right order".

OK, the documentation in this area is wimpy. You could report it at bugs.mysql.com .

I prefer to have (and see) everything inside the CREATE TABLE DDL.

Related Question