What is a clustered index (clustered index)?

A clustered index is a type of index that determines the physical order rows are stored in a table.

That is, the data in the table itself is stored in the order of the clustered index's key values. The table effectively becomes the index.

#The principle

• Each table can have only one clustered index, since rows can only be physically ordered by one field.
• Every other index will be non-clustered (they just store references to the data).

#Example (SQL Server / MySQL InnoDB)

Now the rows in the employees table will be stored in id order.

#Details

• Fast range lookups (BETWEEN, >, <), since the data sits contiguously.
• Fast ORDER BY on that field, the sort order is already there.
• Slower inserts into the middle of the range, since a row has to be "inserted" into its sorted spot.

In InnoDB (MySQL), the clustered index is always the PRIMARY KEY - if there isn't one, the system creates one automatically using an internal identifier.