Does mysqldump retain index information?

Question

Does mysqldump dump index information too, so when a dump is loaded indexes are automatically created? From the documentation I can't see anything that concretely says it does. http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html. Thank you.

Solution 1

Yes. It does. (Do one and you'll see it there).

Solution 2

It depends on what you mean by index information. If you dump the CREATE TABLE statement for each table then this will dump the information about which indexes exist on each table on which columns. It won't dump the contents of each index but this info will be re-created when you replay the INSERTS in the dump file.

If you don't dump the CREATE TABLE statements then it's possible you could lose this info if you re-import the dump file into a schema that doesn't have the indexes in place.

Comments

[Jonas Berlin]

They are disguised as KEY declarations inside a CREATE TABLE statement: CREATE TABLE `foo` ( ... KEY `index1` (`column1`, `column2`), KEY `foo2` (`column3`) );