17

When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually?

The reason that I am asking this question is that when we use UNIQUE INDEX (col1, col2, col3), it acts as INDEX for the first column only, and we need to create additional INDEXs for other columns. I want to know if that's the case for Composite Primary Key too.

Improve this question

1 Answer 1

Reset to default
20

  1. The composite primary index will always ensure that the combination of col1, col2, col3 values are unique. If the purpose of the index is to ensure that the values are unique then you will have achieved that

  2. A composite index also provides for index matches on any combination of the columns col1, col2, and col3

  3. You would create separate indexes on col2, and col3, if you join or filter using any of the columns without the others.

  4. I always prefer a numeric primary key (with no business association), and unique indexes over a composite primary key where necessary.

Improve this answer
10
  • 1
    good points! then, composite primary key is similar to UNIQUE INDEX, and we do not have index for 2nd, 3rd, ... columns for quick fetching a row?
    – Googlebot
    Mar 2, 2012 at 19:23
  • 1
    @ssmusoke Can I assume from the omission of col1 in point 3 that a SELECT * WHERE col1 = 10 would be efficient?
    – Andy
    Jun 17, 2012 at 19:21
  • 2
    @Andy #3 is in addition to a composite index of col1, col2, and col3 so the composite index will be used. However if you do filters on col2, and col3 without col1 then you need separate indexes for each of them alone
    – Stephen Senkomago Musoke
    Jun 18, 2012 at 9:25
  • 3
    Having an index on col1, col2, and col3 means that SELECT * FROM col1 = 10 will be efficient, since col1 is the first column in the index, so #3 allows you to do the same for col2 and col3, so that if you filter by those columns alone without col1 then the columns are also indexed
    – Stephen Senkomago Musoke
    Jun 18, 2012 at 12:15
  • 2
    @PaulWasilewski yes indeed there are special cases where a composite primary key makes sense, but those are just special cases.
    – Stephen Senkomago Musoke
    Apr 4, 2017 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.