If my User table has several fields that are queryable (say DepartmentId, GroupId, RoleId) will it make any speed difference if I create an index for each combination of those fields?
By "queryable", I'm referring to a query screen where the end user can select records based on Department, Group or Role by selecting from a drop-down.
At the moment, I have a index on DepartmentId, GroupId and RoleId. That's a single non-unique index per field.
If an end user selects "anyone in Group B", the SQL looks like:
select * from User where GroupId = 2
Having an index on GroupId should speed that up.
But if the end user select "anyone in Group B and in Role C", the SQL would look like this:
select * from User where GroupId = 2 and RoleId = 3
Having indexes on GroupId and RoleId individually may not make any difference, right?
A better index for that search would be if I had one index spanning both GroupId and RoleId.
But if that's the case, than that would mean that I would need to have an index for every combination of queryable fields. So I would need all these indexes:
- DepartmentId
- GroupId
- RoleId
- DepartmentId and GroupId
- DepartmentId and RoleId
- GroupId and RoleId
- Department Id, GroupId and RoleId
Can anyone shed some light on this? I'm using MySQL if that makes a difference.