CODEWITHSUNDEEP
mysqldatabase
user674669
user674669

Reputation: 12352

Difference between unique and non-unique index in mysql in terms of performance/implementation

I have a simple INNODB mysql table sql_statements

id, text, sha1

id   - primary key
text - variable length strings.           
sha1 - SHA1 hash of the text

text column contains strings like sql query text like "select * from abc", "insert into ...", ...

I have a unique index on the sha1 column to make searching for sql_statements with a given text fast.

However, one limitation of the design is that we can not insert 2 rows with the same sha1 hash.

To get around this, I am considering making the index non-unique.

What is the difference between unique and non-unique indexes in mysql in terms of performance? Both are implemented as B-trees so my guess is performance should be similar.

Thank you.

Upvotes: 0

Views: 3328

Answers (1)

Fabian Barney
Fabian Barney

Reputation: 14549

With a unique constraint the query plan can be optimized to stop on first match. So having a unique index might be faster.

If you're interested in equality queries on the statement-column, then I recommend to use a Hash-Index on that column and drop your own hash column.

In any case I would not use some unique constraint when the column is non-unique just for performance, because it's simply wrong. Furthermore I am curious of what your sql_statements table is used for.

Upvotes: 4

Related Questions