MySQL Queries That Cannot Use Indexes

MySQL is a widely used relational database that relies on indexes to accelerate query performance, but not all SQL statements can take advantage of indexes.

1. Using functions : When a function such as UPPER(name) is applied in the WHERE clause, MySQL cannot use an index.

SELECT * FROM customers WHERE UPPER(name) = 'JOHN';

2. LIKE with leading wildcard : A pattern that starts with % (e.g., '%john') prevents index usage because MySQL can only use indexes for prefix matches.

SELECT * FROM customers WHERE name LIKE '%john';

3. Logical OR operator : Combining conditions with OR (e.g., name = 'john' OR age = 30) disables index usage for the involved columns.

SELECT * FROM customers WHERE name = 'john' OR age = 30;

4. Inequality operators : Using <> or != (e.g., age <> 30) makes the index unusable. SELECT * FROM customers WHERE age <> 30; 5. NULL checks : Conditions that test for NULL (e.g., name IS NULL) cannot be satisfied by an index. SELECT * FROM customers WHERE name IS NULL; To maintain high query performance, developers should avoid these patterns or rewrite the queries so that indexes can be applied.