When will an index not be used during query execution?

The index will not be used in the following cases:

• Functions or operations on an indexable column: if a function is used in the WHERE clause (e.g., UPPER(column) = 'VALUE'), the index is not applied because the values are transformed.

• Inequalities or ranges: for some index types (e.g., B-Tree), when using inequalities (<>, NOT IN) or complex conditions, the index may not be used.

• Low selectivity: if the index covers a column with a small number of unique values, the optimizer may decide that a full table scan is faster.

• Lack of statistics or outdated statistics: the optimizer cannot accurately estimate the effectiveness of the index.

• Using OR with conditions not covered by the index: if part of the condition cannot use the index, the entire index may be ignored.

• Queries selecting a large percentage of rows: when selecting a large volume of data, the index may be less efficient than a full scan.

• Index type not suitable for the query: for example, an index on one column will not help when filtering by another.

Example:

SELECT * FROM users WHERE LOWER(name) = 'ivan'; -- index on name will not be used