Is it necessary to have an index on every combination of queryable fields in a SQL table to optimize performance?

Question

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.

Answer 1

A multi-column index can be used for any left prefix of that index. So, an index on (A, B, C) can be used for queries on (A), (A, B) and (A, B, C), but it cannot, for example, be used for queries on (B) or (B, C).

If the columns are all indexed individually, MySQL (5.0 or later) may also use Index Merge Optimization.

Answer 2

Generally speaking, indexes will increase query speed, but decrease insert/update speed, and increase disk space/overhead. So asking if you should index each combination of columns is like asking if you should optimize every function in your code. It may make some things faster, or it may barely help, and it might just hurt more than it helps.

The effectiveness of indexes depends on:

• Percentage of SELECTs vs. INSERTs and UPDATEs
• The specifics of the SELECT queries, and whether they use JOINs
• Size of table being indexed
• RAM and processor speed
• MySQL settings for how much RAM to use, etc

So, it's hard to give a general answer. The basic sound advice would be: Add indexes if queries are too slow. And remember to use EXPLAIN to see which indexes to add. Note that this is kind of like the database version of the general advice: Profile your app before spending time on optimization.