We have a SELECT query on a date column with a B-Tree index. The query runs slowly, and the plan shows that the index is not used. Why?

Possible reasons if an index B-Tree is not used in a query on the date column:

• The data type of the column and the data type in the query condition do not match, preventing the index from being used.
• The condition uses a function or expression on the date column (e.g., DATE(date) or CAST(date AS ...)), which hinders index utilization.
• The query contains an operator that does not support index usage, such as LIKE '%...%'.
• The table statistics are outdated, and the optimizer decided not to use the index.
• The index does not fully cover the condition or an inappropriate index type is used for this query.

To resolve:

• Ensure that the query condition uses the column directly without functions.
• Check data types.
• Update table statistics.
• Review the query execution plan (EXPLAIN) for diagnostics.