2

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?

  • "PK" that is not clustered is just an UNIQUE key over NOT NULL columns. And there is no INNODB tables with no clustered PK - if no appropriate index exist engine will create the hidden column UNSIGNED INT NOT NULL AUTO_INCREMENT and use it as clustered PK. – Kondybas Aug 28 '18 at 22:11
  • @Kondybas: interesting; in MS SQL (where I'm most familiar), secondary indices reference the PK, whether its clustered or not. Apparently in MySQL, secondary indices reference the clustered key, whether its the PK or merely a unique index. Is that your understanding? – Jon of All Trades Aug 28 '18 at 22:14
  • 2
    As far as I understand Mysql's definition of clustered index : "The InnoDB term for a primary key index." (dev.mysql.com/doc/refman/5.7/en/… ) , primary key is always supported by clustered index , and there is no way to have non-clustered primary key – a1ex07 Aug 28 '18 at 22:16
  • @JonofAllTrades Clustered index is strictly the same as PK for the INNODB. You have to declare the index as PK to make it clustered. There is no way to separate this two properties from each other in the mysql. – Kondybas Aug 28 '18 at 22:37
  • 1
    @JonofAllTrades are you sure? Reading Microsoft docs about non-clustered indexes: "The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key." – ypercubeᵀᴹ Aug 28 '18 at 22:39
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;
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.