I'm migrating a database from DB2 to MySQL. There are big tables, so it takes a huge amount of time. The question here is:

If there's a table with about 9 millions of records with InnoDB storage engine.

What of the next approaches would take less time?

  1. Create the table without indexes, foreign keys or any constraints. Load the data. Alter table to set the constraints

  2. Create the table with constraints and load the data.

In theory the load of the data on an indexed table is slower than on a non indexed table. But creating an index and foreign keys on big tables takes a lot of time.

1 Answer 1


Generally it is better to load an InnoDB table with nothing more than the PRIMARY KEY. Then do one ALTER TABLE to add all the indexes and foreign keys.

If you don't have an explicit PRIMARY KEY, that is bad. Change a UNIQUE key to PRIMARY, if practical.

But, the foreign keys can be a problem because of dependencies.

Plan A: ALTER one table at a time, but in an order such that there cannot be any FK issues.

Plan B: Disable FKs while doing the ALTERs, then enable them.

In InnoDB, the PK is 'clustered' with the data. Hence, the data is sorted by the PK and stored on disk in that order. So, it is best to have the PK defined, and to supply the data to the loader in PK order. The PK+Data is in a BTree.

Secondary keys (and FKs) involve separate BTrees. Building them incrementally can lead to thrashing in the "buffer pool" (see innodb_buffer_pool_size). Building such an index separately can involve a unix sort -- more efficient for really big indexes.

I would call 9M rows rather big (90th percentile), but not huge.

Another caution: UNIQUE keys need to check as they are inserted. I think that implies that you define UNIQUE keys up front, rather than afterwards.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.