Cluster or unique index MySql DB?

Question

I have one interesting question: what is difference between cluster index and unique index? What's better and faster and why?

Have you read e.g. [What do Clustered and Non clustered index actually mean](http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean) or the [mysql documentation](https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html)? If no, do it now. If yes: there is no "better" (especially not without a "for what"). But you should know this by now. If you have a more specific question, you should ask a more specific question. — Solarflare, Oct 03 '16 at 15:01

score 0 · Answer 1 · answered Oct 16 '16 at 18:25

With MySQL's InnoDB engine, you get 3 choices of ordinary indexes:

PRIMARY KEY -- "clustered" with the data and "unique". (Often AUTO_INCREMENT)
UNIQUE -- unique, not clustered.
INDEX -- non unique, not clustered.

All are implemented as BTrees. Clustered means that the data is in the leaf node.

Considerations:

Any of the 3 choices can help with performance of searching for row(s).* InnoDB needs a PRIMARY KEY.
If you want the database to spit at you when you are trying to insert something that is already there, you need to specify the column(s) of the PRIMARY KEY or a UNIQUE.
Secondary keys (UNIQUE or INDEX) go through the PRIMARY KEY to find the data. Corollary 1: Finding a row by the PK is faster. Corollary 2: A bulky (eg, VARCHAR(255)) PK is an extra burden on every secondary key.
A "covering" secondary key does not need to go beyond its BTree.
Data and indexes are cached in RAM at the 16KB block unit; caching may be an important consideration in performance.
UUID/GUID indexes are terrible when the index cannot be fully cached -- due to high I/O.
An INSERT must immediately check the PRIMARY KEY and any UNIQUE key(s) for duplicate, but it can delay updating other secondary keys. (This may have impact on performance during inserts.)

From those details, it is sometimes possible to deduce the answers to your questions.

(Caveat: Engines other than InnoDB have some different characteristics.)

1 Answers1