MySQL 8 Cookbook

Primary key (clustered index) and secondary indexes

Before you proceed further, it is important to understand what a primary key (or clustered index) is, and what a secondary index is.

InnoDB stores rows in a primary key in order to speed up queries and sorts involving the primary key columns. This is also called an index-organized table, in Oracle terms. All other indexes are referred to as secondary keys, which store the value of primary keys (they do not refer to the row directly).

Suppose the table is:

mysql> CREATE TABLE index_example ( col1 int PRIMARY KEY,col2 char(10),KEY `col2`(`col2`));

The table rows are sorted and stored based on the value of col1. If you search for any value of col1, it can directly point to the physical row; ...