Tell me more ×
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. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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

1 Answer

up vote 5 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
@ssmusoke Can I assume from the omission of col1 in point 3 that a SELECT * WHERE col1 = 10 would be efficient? – Andy Jun 17 '12 at 19:21
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 – ssmusoke Jun 18 '12 at 9:25
show 4 more comments

Your Answer

 
discard

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.