Can one make MySQL cluster on a unique index rather than a primary key?

Question

The MySQL docs states that:

When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

But that's not the only possibility, you can cluster off of a unique index instead:

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

It would seem to follow that if I first create a unique index on a table, it will be marked as clustered; I can then create a primary key, and it will be nonclustered:

CREATE TABLE Tmp_CUQTest
(
ID1 INT NOT NULL,  -- Desired clustering field
ID2 INT NOT NULL   -- Desired PK field
);

CREATE UNIQUE INDEX CUC_Tmp ON Tmp_CUQTest (ID1);
ALTER TABLE Tmp_CUQTest ADD CONSTRAINT PRIMARY KEY PK_Tmp (ID2);

However, when I inspect the resulting table, my hopes are dashed:

Key      Type               Uni  Columns
PRIMARY  BTREE (clustered)  YES  ID2
ID1      BTREE              YES  ID1

Another question here on DBA.SE implies that creating an index as part of the CREATE TABLE script, rather than afterward, as a separate CREATE INDEX statement, may make a difference. However, I get the same result.

Is there a way to force MySQL to cluster on a unique index of my choice, other than the primary key, or does this "fallback option" only apply to tables with no primary key at all?

Answer 1

PRIMARY KEY

• InnoDB has exactly one PRIMARY KEY on each table.
• The PRIMARY KEY is always clustered; there cannot be any other "clustered" index.
• That PK is either an explicitly stated PK (this is preferred), the first UNIQUE key (with certain limitations), or a hidden sequence number (not quite the same as `AUTO_INCREMENT; 6 bytes).
• The PK is UNIQUE and is and index.
• The PK (and other indexes) may contain multiple columns ("composite" index.
• The table is structured as a BTree (actually a B+Tree), with all columns present, and BTree is ordered according to the PK.

Secondary Keys

• All other indexes are called "secondary indexes". ("key" == "index")
• The structure is a B+Tree, ordered according to the key
• The "rows" of this BTree contain copies of the column(s) of the PRIMARY KEY (that is, the 'clustered index'). This is how the secondary key can get to the row.
• Note that looking up a row via a secondary key requires drilling down two BTrees. (Unless it is a "covering" index).
• A UNIQUE KEY is two things: a secondary key (unless promoted to PK), and a "uniqueness constraint".

Other notes

• InnoDB has not concept of a "ROWNUM" or "row locator", unlike some other vendors. The PK serves as the row locator.
• MySQL has no "Heap" or "Bit" indexes on InnoDB.
• InnoDB has only BTree, SPATIAL, and FULLTEXT; I am not discussion the latter two in this Answer.
• For most purposes, it does not matter whether you create the indexes inside the CREATE TABLE statement, or do CREATE INDEX later. Ditto for defining FOREIGN KEYs.
• Structurally, the PK+data is a B+Tree; each secondary index is a B+Tree. These B+Trees look, feel, smell, etc, identical. The only difference is what you find in the 'rows'.

AUTO_INCREMENT

• There are many caveats. It is not necessarily consecutive numbers; there are many ways that gaps can occur. Only trust that they will be 'unique'.
• AUTO_INCREMENT does not have to be declared PRIMARY KEY, or even UNIQUE.

A trick. It is possible to do the following. Is it what you are fishing for??

CREATE TABLE ... (
  id INT AUTO_INCREMENT,
  foo ...,
  ...
  PRIMARY KEY (foo, id),  -- to get clustering on `foo` and uniqueness (via `id`)
  INDEX(id)               -- sufficient to keep AUTO_INCREMENT happy.
) ENGINE=InnoDB;

Answer 2

A primary key is a unique key and an index. So create the PK as the unique index that you want to cluster on.

Create adding an index afterwards and as part of the create table yeild the same result.