When an indexed column that is part of a multi-column index is being queried and the other indexed columns that are part of the same index are not part of the query, is the index invoked?
There are several factors to consider when answering this question, including the position of the column that is being queried within the definition of the index.
When working with multi-column indices, SQL databases only use the index in an efficient manner if the column that is being queried is the leftmost in the definition of the index, also known as the “leftmost prefix rule”. For example, if you have and index on three columns (col1, col2, col3):
- Queries filtering on col1 can use the index.
- Queries filtering on col1 and col2 can use the index.
- Queries filtering on all three columns can use the index.
- Queries filtering on col2 alone typically cannot use the index efficiently.
- Queries filtering on col3 alone typically cannot use the index efficiently.
The optimizer in the database makes the decision about how the index will be used based on:
- Whether your query uses leftmost columns of the index.
- Selectivity of the columns (how many rows will be filtered).
- Table size.
- Other available indices.
- Query complexity.
In order to find out if an index is being used, is to use the database’s execution plan.