7.5.11 Table and Index Structures

MySQL stores its data dictionary information of tables in `.frm' files in database directories. But every InnoDB type table also has its own entry in InnoDB internal data dictionaries 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 you cannot move InnoDB tables between databases simply by moving the `.frm' files, and why DROP DATABASE did not work for InnoDB type tables in MySQL versions <= 3.23.43.

Every InnoDB table has a special index called the clustered index where the data of the rows is stored. If you define a PRIMARY KEY on your table, then the index of the primary key will be the clustered index.

If you do not define a primary key for your table, InnoDB will internally generate a clustered index where the rows are ordered by the row id InnoDB assigns to the rows in such a table. The row id is a 6-byte field which monotonically increases as new rows are inserted. Thus the rows ordered by the row id will be physically in the insertion order.

Accessing a row through the clustered index is fast, because the row data will be on the same page where the index search leads us. In many databases the data is traditionally stored on a different page from the index record. If a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution.

The records in non-clustered indexes (we also call them secondary indexes), in InnoDB contain the primary key value for the row. InnoDB uses this primary key value to search for the row from the clustered index. Note that if the primary key is long, the secondary indexes will use more space.

Subsections

• 7.5.11.1 Physical Structure of an Index
• 7.5.11.2 Insert Buffering
• 7.5.11.3 Adaptive Hash Indexes
• 7.5.11.4 Physical Record Structure
• 7.5.11.5 How an Auto-increment Column Works in InnoDB

User Comments

The last paragraph suddnely introduces a new phrase, 'non-clustered indexes'. What is that? In what situations are they used?

non-clustered indexes are indexes that aren't primary keys ( when one talks about an 'index' you mean these ones). They are used to accelerate table access by using another field than the PK of the table :)

Add your own comment.