CODEWITHSUNDEEP
mysql
Beier
Beier

Reputation: 3157

mysql unique key and index

in MySQL, If I create a unique key on two columns, say

UNIQUE KEY my_key (column1, column2)

is it necessary to build another key on column1? My goal is to have column1 and column2 to be unique, and I'll do lots of selects keyed by column1.

Upvotes: 8

Views: 10417

Answers (3)

Frank Heikens
Frank Heikens

Reputation: 127476

A UNIQUE-index is a btree-index. This index is sorted and that's why you don't need a second index on the first colummn. The sort order of the combination will also work for only the first column, but not for only the second column.

Upvotes: 2

PatrikAkerstrand
PatrikAkerstrand

Reputation: 45731

No, your my_key index takes care of any queries on column1 or conditions on column1 AND column2. However, if you make queries on only column2, then you should add an additional index for column2 to be able to query it efficiently.

Furthermore, if both column1 and column2 are unique, then you might want to consider using something like

[...]
UNIQUE(column1),
UNIQUE(column2),
PRIMARY KEY (column1, column2);

This ensures that both column1 and column2 are unique, and any query selecting only column1 and column2 can be retrieved using index-only access.

Upvotes: 8

Mark Byers
Mark Byers

Reputation: 839114

No, it is not necessary because an index on (column1, column2) can be used instead of an index of column1 alone.

You might want to make a new index on just column two though as the index (column1, column2) is not good when searching only for (column2).

Upvotes: 12

Related Questions