Clustered index and secondary indexes

Every InnoDB table has a special index called the clustered index where the data for the rows are stored.

If you are define a PRIMARY key on your table, the index of the PRIMARY key is the clustered index.

Each InnoDB table has a specific index called clustered index, which stores the data for the row.

If you don't define a PRIMARY KEY for your table, MySQL picks the ' the ' the ' the ' the ' the ' the ' the ' the ' only ' not NULL has as th E primary KEY and InnoDB uses it as the clustered index. If there is no

Such index in the table, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID Valu Es. The rows are ordered by the ID this InnoDB assigns to the rows in such

a table.

The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

If your table does not have a primary key defined, MySQL chooses the first unique index with the not NULL column as the primary key, InnoDB uses it as the clustered index.

If there is no such index in the table, InnoDB internally generates a hidden clustered index in the virtual column containing the row ID value.

Records are sorted by ID, InnoDB specifies that the row is in one table.

The row ID is a 6-byte column that automatically increases when a row record is inserted

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If A table is large, the clustered index architecture often saves a disk

I/O operation when compared to storage organizations this store row data using a different page from the index record. (for example, MyISAM uses one file for data rows and another for index

Records.)

The clustered index access record is fast because the record data is on the same page when the index is searched. If the table is large, the clustered index structure often saves a large amount of disk I/O operations compared to the records stored by the storage engine organization

Use a different page.

For example, MyISAM uses one file to store data and another file to store index records

Physically, the InnoDB table consists of shared table spaces, log file groups (redo filegroups), and table-structured definition files. If you set innodb_file_per_table to ON, the system will generate a TABLE_NAME.IBD file for each table separately, in this file, the table

Related data, indexes, and the internal data dictionary information of the table. The table structure file ends with. frm, regardless of the storage engine.

In InnoDB, the "Records in Non-clustered indexes" also the called Secondary key value for the row.

InnoDB uses this primary key value to search for the row in the clustered index. If The primary key is long, the secondary indexes

So it's advantageous to have a short primary key.

In InnoDB, records are recorded in non-clustered indexes (also known as secondary indexes) that contain primary key values.

InnoDB uses primary key value to search for records in clustered index, if primary key is too long,

Secondary indexes use more space,
Here's secondary index

The second index is a non primary key index