Table And Index Structure

MySQL stores its data dictionary information for tables in .frm files in database directories.
- This is true for all MySQL storage engines.
- Every InnoDB table also has its own entry in the InnoDB internal data dictionary inside the tablespace.
- When MySQL drops a table or a database, it has to delete both a .frm file or files, and the corresponding entries inside the InnoDB data dictionary.
  - This is the reason why InnoDB tables cannot be moved between databases simply by moving the .frm files.

Every InnoDB table has a special index called the clustered index where the data for the rows is stored.
- If a PRIMARY KEY is defined on a table, the index of the primary key is the clustered index.
- If a PRIMARY KEY is not defined on a table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.
  - If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table.

What is InnoDB disk I/O?