What is stored in index leaf nodes when a table has only secondary index?

December 1, 2022

If a table has a clustered index and a secondary index, the leaf nodes in secondary index contain the attribute value of the clustered index. But what if a table only has non-clustered index? How can a non-clustered index retrieve data without a clustered index?

create table table_without_primary_key(
    name varchar(30) not null ,
    date datetime not null
);

insert into table_without_primary_key
values ('jack',now());
insert into table_without_primary_key
values ('alice',now());
insert into table_without_primary_key
values ('ribbon',now());

create index time_index
on table_without_primary_key (date);
show index from table_without_primary_key;

Result:

+---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table                     | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| table_without_primary_key |          1 | time_index |            1 | date        | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.02 sec)

Besides, I also heard from somewhere that if a table does not have primary key or unique index, it will use row id to create a clustered index.

But I doubt if it is true, since I found no automatic clustered index on row id from the table I described above.

>Solution :

All InnoDB tables are stored as a clustered index.

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html says:

If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values. …