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.
-
1just did an experiment using EXPLAIN, it seems I don't have to create another key on column1, as mysql will use the unique key when doing select keyed by column1.Beier– Beier2010-03-10 21:21:02 +00:00Commented Mar 10, 2010 at 21:21
Add a comment
|
3 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).
Comments
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.
Comments
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.
