GNU Info

(mysql.info)Table and index

---

Next: File space management Prev: Implementation Up: InnoDB

---

Enter node , (file) or (file)node

Table and index structures
--------------------------

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.

InnoDB physical structure

Physical structure of an index

InnoDB Insert buffering

Insert buffering

InnoDB Adaptive hash

Adaptive hash indexes

InnoDB Physical record

Physical record structure