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?
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.