The $0 Fix: Why Your MySQL Indexes Aren't Working

TL;DR: Stop Blaming the Database

• Wrapping an indexed column in a function (DATE(col), UPPER(col)) forces a full table scan. The optimizer can't use the index because it doesn't know what the function will return.
• Comparing an indexed VARCHAR column to a number (WHERE phone_number = 1234567890) causes an implicit type conversion on every row, disabling the index. The fix is to use a string literal: WHERE phone_number = '1234567890'.
• These inefficient queries often run fast enough to miss your slow_query_log but burn 100% of your CPU by examining millions of rows instead of one. This is death by a thousand cuts.

---

I’ve lost count of the number of times I’ve seen engineering teams throw hardware at a performance problem that could be fixed with a single line of code. They see p99 latency climbing, CPU pegged at 100%, and they panic. Scale up the RDS instance. Add read replicas. It’s a temporary fix for a permanent problem.

The real issue is almost always hiding in plain sight: the database is doing exactly what you told it to do. Your queries are written in a way that actively prevents the MySQL optimizer from using the very indexes you created to make them fast.

The index is there. It's correct. The optimizer just can't see it.

This happens when your WHERE clause contains what we call a non-SARGable predicate. SARG stands for "Search-Argument-able," a term from the System R days that's stuck around. It means the predicate can be resolved by an index search. A non-SARGable predicate forces the database to fetch every row from the table and apply the condition, one by one. This is the dreaded type: ALL in an EXPLAIN plan—a full table scan.

Let's walk through the most common, and most damaging, examples I see in the wild.

Mistake 1: Implicit Type Conversion — The Silent Killer

This is the most common one. Your app has a users table with a phone_number column, correctly defined as VARCHAR(20). There’s an index on it. A developer writes a simple lookup.

-- The table structure
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  phone_number VARCHAR(20),
  created_at DATETIME,
  INDEX idx_phone (phone_number)
) ENGINE=InnoDB;

-- Assume 4.2 million rows

-- The developer's query
SELECT id, name
FROM users
WHERE phone_number = 9876543210;

This query will be slow. Not five seconds slow, but maybe 250ms slow. Slow enough to tank your service's latency under load, but fast enough to fly under the long_query_time radar. The result is a system with 100% CPU utilization and no obvious slow queries.

Here’s the EXPLAIN:

+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | idx_phone     | NULL | NULL    | NULL | 4205123 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

Look at that. type: ALL. possible_keys sees our index, idx_phone, but key is NULL. MySQL chose not to use it. It examined 4.2 million rows. Why?

Because you asked it to compare a VARCHAR column to an integer literal. MySQL can't compare a string ('9876543210') to a number (9876543210) directly. Its type precedence rules dictate that it must convert the string in the column to a number for every single row. The query effectively becomes:

WHERE CONVERT(phone_number, UNSIGNED) = 9876543210;

The index idx_phone is a B-Tree built on the string representation of the phone numbers. It's useless for evaluating the result of CONVERT(). The optimizer has no choice but to scan the entire table, apply the function, and then check the condition.

The fix is trivial. Just put quotes around the number.

-- The correct, SARGable query
SELECT id, name
FROM users
WHERE phone_number = '9876543210';

Now, the EXPLAIN tells a different story:

+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_phone     | idx_phone | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

type: ref. rows: 1. Latency: sub-millisecond. We just went from scanning 4.2 million rows to one direct index lookup. This is the difference between a service that falls over at 100 RPS and one that handles 10,000 RPS on the same hardware.

Mistake 2: Wrapping Indexed Columns in Functions

This is just as common and just as deadly. You have an orders table with a DATETIME column, created_at, and it's indexed. You want to find all orders from a specific day.

-- Indexed DATETIME column
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT,
  amount DECIMAL(10,2),
  created_at DATETIME,
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Assume 15 million rows

-- The intuitive, but wrong, query
SELECT id, amount FROM orders
WHERE DATE(created_at) = '2026-06-01';

This feels natural to write. But you just forced a full table scan. The EXPLAIN is ugly:

+----+-------------+--------+------------+------+----------------+------+---------+------+----------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | idx_created_at | NULL | NULL    | NULL | 15000000 |    10.00 | Using where |
+----+-------------+--------+------------+------+----------------+------+---------+------+----------+----------+-------------+

Again, type: ALL. The optimizer has no way to use the idx_created_at B-Tree. The index stores the full DATETIME values. The optimizer can't possibly know which of those values will equal '2026-06-01' after being passed through the DATE() function, so it can't seek into the index. It must load every row, execute the function, and then perform the comparison.

The correct way to write this is to express it as a range query on the raw, indexed column.

-- The correct, SARGable range query
SELECT id, amount
FROM orders
WHERE created_at >= '2026-06-01 00:00:00'
  AND created_at <  '2026-06-02 00:00:00';

This is SARGable. The optimizer can use the index to find the first entry for June 1st, scan through all entries for that day, and stop when it hits June 2nd. The EXPLAIN proves it:

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | orders | NULL       | range | idx_created_at | idx_created_at | 5       | NULL | 86432 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+

type: range. rows examined dropped from 15 million to just 86k. This query is orders of magnitude more efficient. The same logic applies to UPPER(), LOWER(), CAST(), CONCAT(), and any other function you apply to an indexed column in the WHERE clause.

Bonus Mistake: Mismatched Collations on JOIN

You can even trigger an implicit conversion on a JOIN. Imagine joining users.email to actions.user_email. Both are VARCHAR, both are indexed.

If users.email is utf8mb4_0900_ai_ci and actions.user_email is utf8mb4_general_ci, MySQL must convert one side to match the other on every row comparison. Your index on one of those tables is now useless for the join. This is a subtle bug that can silently cripple performance on a core join in your application. Check your collations. They must match exactly.

What DeepSQL does about this

Fighting this by hand requires constant vigilance. It requires code reviews by engineers who understand SARGability and have the discipline to run EXPLAIN on every new query. Most teams don't have that.

DeepSQL automates this entire process. It connects to your database and reads the schema, including column types, character sets, and collations. It then analyzes the live query workload from the performance schema. When it sees a query like WHERE phone_number = 9876543210, it knows phone_number is a VARCHAR. It generates the semantically equivalent, SARGable rewrite—WHERE phone_number = '9876543210'. Before suggesting the fix, it validates it by running EXPLAIN on both the original and rewritten query, confirming the plan improves from type: ALL to type: ref and that rows examined drops dramatically. It's a context-aware rewrite engine that turns these hidden performance bombs into actionable pull requests.