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.

Answers


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).


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.


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.


Need Your Help

Writing test case in Rspec to handle syntax error

ruby-on-rails rspec rspec-rails

I am new to Rspec. I am writing a spec for a controller. Is it possible to catch syntax errors in the code with spec i.e. I would like to write case for handling syntax error in controller with Rsp...