开发者

when I use something like unique key(element1,element2) how does it work internally?

  1. If I say index(element1), index(element2) does it use much less space than unique key(element1,element2)?
  2. I know what they do is different. My understanding is that unique key(element1,element2) ensures that there are no duplicates where those 2 rows are the same. Is this correct?
  3. Does it still index both keys individually?
  4. Bu开发者_JS百科t is this expensive in terms of disk space and checking to create such and index?
  5. Maybe it's better to not have it if it's not critical there are no duplicates?


  1. An INDEX(a,b) uses less space than two indexes INDEX(a) and INDEX(b), because each index consists of (a part of) that column and the primary key. But read the note below about the functional difference between these indices.
  2. Correct. A UNIQUE KEY makes that no 2 rows have the same values for the columns in that key.
  3. A UNIQUE INDEX is also an INDEX and can be used for searching. A special example of a UNIQUE KEY is the PRIMARY KEY.
  4. Indexes do take up space on the disk, depending on your Storage Engine. If your application is write-heavy (like a logging table), sometimes it might be better to not have an index. Most tables are probably read-heavy though.
  5. From a logical point of view, if it's not critical there are no duplicates, don't put the index.

Edit: elaboration on pst's comment:

If you have INDEX(A), INDEX(B) and INDEX(A,B), your INDEX(A) is redundant. Drop it But INDEX(A,B) does not cover queries that only search on B, you need an INDEX(B) for that.

You can argument that INDEX(A) and INDEX(B) together can use MySQL's INDEX MERGE to form the INDEX(A,B). This leaves you the choice between

  1. INDEX(A,B) and INDEX(B)
  2. INDEX(A) and INDEX(B)

Solution 2 will take less disk-space, that is true. But read this Very Nice MySQLPerformanceBlog article about INDEX MERGE, which comes to this conclusion:

As a summary: Use multi column indexes is typically best idea if you use AND between such columns in where clause. Index merge does helps performance but it is far from performance of combined index in this case. In case you're using OR between columns - single column indexes are required for index merge to work and combined indexes can't be used for such queries.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜