11

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.

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

  • +1 for these points, especially for point #2 in the event queries have GROUP BY clauses & ORDER BY clauses that need different compound index combinations. – RolandoMySQLDBA Mar 2 '12 at 19:19
  • 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 '12 at 19:23
  • 1
    @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 '12 at 9:25
  • 2
    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 '12 at 12:15
  • 1
    @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 '17 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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