Question 21984 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

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

    The row ID is a 6-byte field that increases monotonically as new rows are inserted.

    Login or Signup to reply.
  2. 0

    In addition to what Bill says, and tested on 8.0.31-0ubuntu0.22.04.1:

    SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'table_without_primary_key' ;
+---------------+---------------------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name                | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+---------------------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| try           | table_without_primary_key | GEN_CLUST_INDEX | 2022-12-01 11:39:23 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| try           | table_without_primary_key | GEN_CLUST_INDEX | 2022-12-01 11:39:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| try           | table_without_primary_key | GEN_CLUST_INDEX | 2022-12-01 11:39:23 | size         |          1 |        NULL | Number of pages in the index      |
| try           | table_without_primary_key | time_index      | 2022-12-01 11:39:23 | n_diff_pfx01 |          1 |           1 | date                              |
| try           | table_without_primary_key | time_index      | 2022-12-01 11:39:23 | n_diff_pfx02 |          3 |           1 | date,DB_ROW_ID                    |
| try           | table_without_primary_key | time_index      | 2022-12-01 11:39:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| try           | table_without_primary_key | time_index      | 2022-12-01 11:39:23 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+---------------------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.01 sec)

    Note that it does have a PRIMARY KEY called GEN_CLUST_INDEX.

    A ‘row’ in a secondary index will have

    1. The column(s) of the secondary index, in the order specified.
    2. Any other columns that are in the PRIMARY KEY. As already noted, there will always be a PK, but you might see it as a UNIQUE or the hidden 6-byte thingie.

    There will also be B+Tree overhead stuff, such as links to other nodes, both at the same and other levels.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search