I have a UserSkills table having three columns: id (PK), userId (FK) and skillId (FK).
I want to enforce a composite unique constraint on a combination of userId and skillId. And for faster lookups, I want composite indexing on (userId, skillId).
As far as I know, MySQL automatically indexes unique columns. But, all the examples I have seen and my own implementations so far involve single-column unique constraints. I want to know if MySQL indexes composite unique keys as well. And if it does, is it treated as a normal multi-column index or not?
For normal multi-column indexes, according to the MySQL reference manual:
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
In a nutshell, I want to find out if I should create a multi-column index of (userId, skillId) after declaring the aforementioned multi-column unique constraint involving them, or would that be simply redundant and hence not required?
So for your fields, id (PK), userId (FK) and skillId (FK),
mysql will automatically create an index on (id) (unique), and index on (userId) (non-unique) and an index on (skillId) (non-unique).
You still need an additional unique index on (userId, skillId).
This could replace the non-unique index on (userId) because the optimizer can use the (userId, skillId) index whenever it needs to look up by userId. However, creating and dropping indexes when the index is used by a foreign key in mysql can be cumbersome, so you might just want to add the composite unique index.