HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

How is INDEX on Composite Primary Key in mysql?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
primarymysqlcompositehowindexkey

Problem

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.

Solution

-
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

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

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

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

Context

StackExchange Database Administrators Q#14256, answer score: 20

Revisions (0)

No revisions yet.