patternsqlMajor
How is INDEX on Composite Primary Key in mysql?
Viewed 0 times
primarymysqlcompositehowindexkey
Problem
When making a composite primary key for two or more columns, e.g.
The reason that I am asking this question is that when we use
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.
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.