Is there any performance difference between UNIQUE INDEX and INDEX with the same cardinality on MySQL?

Question

I have a table that has id and created_at.

Column     | Non Unique | Cardinality
-------------------------------------
id         | 0          | 1000
created_at | 1          | 1000

id is unique, and created_at is non unique.

I want to know if there's any performance improvement if I convert the created_at index from non unique to unique.

Answer 1

There will be no difference for read operations, once written the entries in the index will be the same.

In theory there is a slight difference in update performance as the engine needs to enforce uniqueness in a unique index, but in reality this is one going to be at most a few CPU cycles per row difference so will be unnoticeable.

Only create a unique index for values that you know should be unique. It is quite conceivable that two items could be created at the same time (or at least within the granularity of the data type you are using for that column).

Answer 2

First, I must emphasize that you should use UNIQUE when you need the uniqueness check, not for performance considerations.

That said, here are the small performance considerations:

INSERT checks for uniqueness before acknowledging the insert. This slows down the response time (a little) for INSERTs. A plain INDEX is delayed and does not actually get performed until later. See "Change buffering". This speeds up non-unique index maintenance (a little).

A SELECT, when using a non-unique index will (unless something else stops it), will read rows until the value is different. With a UNIQUE index, it knows to stop after one row is found. (Again, a minor performance difference.)