Is there a situation where MySQL creates a nonclustered index automatically?

Question

MySQL creates a clustered index automatically when I create a primary key.

But is there a situation where MySQL creates a nonclustered index automatically?

MySQL creates a clustered index automatically when I create a primary key. No, it creates clustered index always. If there is no PRIMARY index, first UNIQUE is used. If it abcent too, internal hidden record number is used. is there a situation where MySQL creates a nonclustered index automatically? No when you mean table index. — Akina, Commented Dec 3, 2018 at 18:51

Rick James · Accepted Answer · 2018-12-03 23:29:10Z

The PRIMARY KEY is a UNIQUE key; it is also "clustered". (Note: Clustered)
In the absence of an explicit PK, the 'first' UNIQUE key containing only non-NULL column(s) is effectively promoted to be the PK.
If that fails, a hidden 6-byte number is used for the PK.
A UNIQUE key is an INDEX, plus a uniqueness constraint. (Note: may be clustered)
An INDEX is a BTree that is used for efficient access.
A FOREIGN key creates an INDEX if there is not already a suitable index. (Note: Non-clustered)

The 3 "Notes" are implicitly-created indexes, but not necessarily non-clustered.

1 Answer 1