Why is my database query slow even with an index?

Updated October 2026 · How we answer

Short answerAn index may not be used due to low selectivity, function wrapping, type mismatches, or outdated statistics. Check the query plan to see if the index is actually used.

Common reasons an index is ignored

The database optimizer might skip your index if it estimates that a full table scan is cheaper, such as when the column has low cardinality (few distinct values) or when the query returns a large fraction of the table. Wrapping the indexed column in a function (e.g., WHERE UPPER(name) = 'JOHN') also prevents index usage unless you have a function-based index.

Data type mismatches can cause implicit conversions that disable the index. For example, comparing a string column to a number may force a cast on every row. Outdated statistics can also lead the optimizer to make poor choices, so running ANALYZE or UPDATE STATISTICS regularly helps.

  • Low selectivity: index on a column with many duplicate values
  • Function or expression on the indexed column in WHERE clause
  • Implicit type conversion between column and literal
  • Stale statistics causing bad optimizer estimates
  • Index not covering all columns needed by the query

How to diagnose and fix

Start by examining the query execution plan (EXPLAIN or EXPLAIN ANALYZE in PostgreSQL, EXPLAIN in MySQL). Look for operations like 'Seq Scan' or 'Full Table Scan' where you expected an index scan. The plan shows which indexes, if any, are used and the estimated cost.

If the index isn't used, try rewriting the query to avoid functions on indexed columns, ensure data types match, and update statistics. Sometimes a composite index or a covering index that includes all selected columns can help. For complex queries, consider adding a hint (if your database supports it) or restructuring the query.

  • Run EXPLAIN ANALYZE to see actual vs. estimated rows
  • Check for missing or unused indexes
  • Update statistics with ANALYZE or similar
  • Consider composite or covering indexes
  • Avoid SELECT *; fetch only needed columns

Common mistakes

  • Assuming an index always speeds up queries; it can slow down writes and may be ignored by the optimizer.
  • Forgetting that indexes on expressions require a matching function-based index.
  • Overlooking that ORMs may generate queries that prevent index usage, such as using LIKE with a leading wildcard.
From our shopsSwiftCase: Curated phone cases that ship in 48 hours.