O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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; ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required