Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 12 down vote accepted
  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.

share|improve this answer
+1 for great outline. – Derek Downey Mar 2 '12 at 16:40
+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? – All Mar 2 '12 at 19:23
@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
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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