Why the primary key is not the clustered index if another non clustered index is added in MariaDB

Question

Hello I have a table created by the following query MariaDB version 10.5.9

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_status_IDX` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

I always thought that the primary key is by default the clustered index which also defines the order of the rows in the table but here it seems that the index on the status is picked as the clustered. Why is this happening and how can I change it?

MariaDB [test]> select * from test;
+----+--------+
| id | status |
+----+--------+
|  2 | cfrc   |
|  5 | hjr    |
|  1 | or     |
|  3 | test   |
|  6 | verve  |
|  4 | yes    |
+----+--------+
6 rows in set (0.001 sec)

score 3 · Accepted Answer · answered Feb 02 '22 at 12:32

It is not safe to assume that the results of SELECT will be ordered by any column across dB engines. You should always use ORDER BY col [ASC|DESC] if you expect sorting to happen. I see records being displayed in the order they were added, but that can change after deletions/insertions etc, and should not be relied on. See here for more details.

score 1 · Answer 2 · answered Feb 02 '22 at 14:23

@aprsa is right I falsely assumed that the results will be in the same order as the clustered index but in this case(using INNODB) the status index is used for the query's evaluation so that's why it appears to be 'sorted' by the status. If I select the id then the primary index is used and the results appear to be 'sorted' by the id. In another engine this might not be true.

score 1 · Answer 3 · answered Feb 03 '22 at 21:59

That particular table is composed of 2 BTrees:

The data, sorted by the PRIMARY KEY. Yes, it is clustered and is ordered 1,2,3,...
The secondary index, sorted by status. Each secondary index contains a copy of the PK so that it can reach into the other BTree to get the rest of the columns (not that there are any more!). That is, the is BTree is equivalent to a 2-column table with PRIMARY KEY(status) plus an id.

Note how the output is in status order. I have to assume it decided to simply read the secondary index in its order to provide the results.

Yes, you must specify an ORDER BY if you want a particular ordering. You must not assume the details I just discussed. Who knows, tomorrow there may be something else going, such as an in-memory "hash" that has the information scrambled in some other way!

(This Answer applies to both MySQL and MariaDB. However, MariaDB is already playing a game with hashing that MySQL has not yet picked up. Be warned! Or simply add an ORDER BY.)

Why the primary key is not the clustered index if another non clustered index is added in MariaDB

3 Answers3