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?