stack stack - 1 year ago 58
SQL Question

A unique can be used as index?

I have this table:

// votes
| id | user_id | post_id |
| 1 | 12345 | 12 |
| 2 | 12345 | 13 |
| 3 | 52344 | 12 |

Also this is a part of my query:

EXISTS (select 1 from votes v where = v.user_id and = v.post_id)

To make my query more efficient, I have added a index group on

ALTER TABLE `votes` ADD INDEX `user_id,post_id` (`user_id,post_id`)

What's my question? I also want to prevent of duplicate vote from one user to one post. So I have to create a unique index on
too. Now I want to know, should I create another index? or just a unique index is enough and I should remove previous index?

Answer Source

You do not need two indexes serving similar purpose. Only one of them would be used during a select operation, and both will have to be modified on insert, update and delete. These are unnecessary overheads. Go with the unique index, since it serves both the purposes. A range scan is almost guaranteed when using a unique indexed columns in a where clause.


The term for index does not matter. When you are creating an index, a tree structure is created, selected a convenient root node, and rearranging column values. If all entries in the given column are going to be unique, normal index would also be of the same size as unique index, and would give same performance as unique index.

Primary index is also a unique index, with the exception that it would not allow null values.Null values are permitted in a unique index.