MySQL Queries That Cannot Use Indexes

MySQL is a widely used relational database that relies on indexes to speed up queries, but certain SQL statements prevent index usage.

1. Using functions – When a function such as UPPER() is applied to a column in the WHERE clause, MySQL cannot use the index. SELECT * FROM customers WHERE UPPER(name) = 'JOHN'; 2. Using LIKE with a leading wildcard – A LIKE pattern that starts with % (e.g., '%john') forces a full scan because MySQL can only use indexes for prefix matches. SELECT * FROM customers WHERE name LIKE '%john'; 3. Using the OR logical operator – When an OR combines conditions on different columns, MySQL may abandon the index for a table scan. SELECT * FROM customers WHERE name = 'john' OR age = 30; 4. Using inequality operators – Conditions with <> or != prevent index usage because they require scanning all rows to find non‑matching values. SELECT * FROM customers WHERE age <> 30; 5. Using NULL checks – Queries that test for NULL with IS NULL cannot leverage indexes, as indexes do not store missing values. SELECT * FROM customers WHERE name IS NULL; To improve performance, avoid these patterns or rewrite the queries so that indexes can be applied.