Search code examples
mysqlsqldatabasequery-performance

Difference for SELECT querys speed between these indexes: *UNIQUE(col1, col2)* vs *FK(col1), FK(col2)*

UNIQUE(col1, col2) vs FK(col1), FK(col2)

I currently have both indexes enabled, and I don't really care much about de foreign relationship per sé. So I wonder if by having all those keys I'm getting redundant indexes for reading performance, or are they different in some edge cases?

I know these two columns are a good candidate for a primary key, but as a team we all decided to always have an autoincremental as a Primary key.

Solution

  • MySQL wants to create a separate index for each of these. So you might have three indexes on :

    • (col1, col2)
    • (col1)
    • (col2)

    I say "might" because MySQL also tries to optimize the automatic index creation. So, if the first index is already created, then the second is not needed -- they are redundant. The third is needed, though.

    My recommendation? The automatic index creation supports other purposes -- enforcing a unique constraint for the first index or enforcing foreign key constraints for the second and third. You want the database to do all of these. Keep the unique and foreign key declarations!

    So, the only thing you can do is ensure that the unique index is known about before the foreign keys are declared. It should be sufficient to declare it in the create table -- however, I would put the unique constraint before the foreign key constraints just to be sure.