A clustered index controls the physical order of rows on storage media. Without an index or constraint, rows have no particular order, so queries may need to read every row to find matching data. section 19.3.1 section 19.3
A telephone-book analogy
A telephone book is sorted by surname and then first name, with each person’s telephone number and address beside the name. If the book were a table, that sorting would be its clustered index. Because rows can be physically ordered only one way, a table can have only one clustered index. section 19.3.1
Keep these practical points in mind:
- The clustered index effectively becomes the table rather than remaining a separate object. section 19.3.1
- Choose columns that are commonly used, because the table can be sorted in only one way. section 19.3.1
- Primary-key columns are common choices because joins often read related primary and foreign key values. section 19.3.1
- In MySQL, creating a PRIMARY KEY for the shipment table automatically created its clustered index. section 19.3.1
Figure 19.1 The three indexes of the shipment table in the sqlnovel database, viewed in MySQL Workbench
Inspecting the shipment table’s indexes in MySQL Workbench helps connect the clustered-index idea with a concrete database design. section 19.3.1
