MySQL ignored our phone number index because the app passed an integer

Our support tool looks up customers by phone number when they call. It had always been instant. After a deploy, agents complained that the search box took several seconds to respond, and the database's slow query log filled up with one statement:

# Time: 2026-10-22T09:12:44.118302Z
# Query_time: 3.104512  Lock_time: 0.000004  Rows_sent: 1  Rows_examined: 8214407
SET timestamp=1792660364;
SELECT id, name, email FROM customers WHERE phone = 4915112345678;

Rows_examined: 8214407 for Rows_sent: 1 is a full table scan. But there was an index on phone, and it had not been touched.

The table definition:

CREATE TABLE customers (
  id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(200) NOT NULL,
  email   VARCHAR(255) NOT NULL,
  phone   VARCHAR(20)  NOT NULL,
  ...
  KEY idx_customers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Look at the query again: phone = 4915112345678, no quotes. The column is a string; the value is a number.

EXPLAIN, before and after quoting

EXPLAIN SELECT id, name, email FROM customers WHERE phone = 4915112345678;

+----+-------------+-----------+------+---------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | type | possible_keys       | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------+---------------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | customers | ALL  | idx_customers_phone | NULL | NULL    | NULL | 8102331 |    10.00 | Using where |
+----+-------------+-----------+------+---------------------+------+---------+------+---------+----------+-------------+

possible_keys lists the index, but key is NULL and type is ALL: the optimiser considered the index and could not use it. With the value quoted:

EXPLAIN SELECT id, name, email FROM customers WHERE phone = '4915112345678';

| 1 | SIMPLE | customers | ref | idx_customers_phone | idx_customers_phone | 82 | const | 1 | 100.00 | NULL |

type: ref, one row, the index used. MySQL also leaves a hint in the warnings after the first EXPLAIN:

SHOW WARNINGS;

| Warning | 1739 | Cannot use ref access on index 'idx_customers_phone' due to type or collation conversion on field 'phone' |

Where the integer came from

The deploy had replaced a hand-written query builder with an ORM repository method. The search input was normalised to digits only and then cast:

$digits = preg_replace('/\D+/', '', $input);
$customer = $repo->findOneBy(['phone' => (int) $digits]);

The (int) cast was added because a code style tool complained about a mixed type. The ORM bound the parameter as an integer, so the statement sent to MySQL compared a VARCHAR column with an integer literal.

How MySQL compares a string with a number

When the two sides of a comparison have different types, MySQL converts them to a common type first. For a string compared with an integer, the rule is that both are compared as numbers (floating point). So for every row, MySQL must take phone, convert the string to a number, and compare it to 4915112345678.

That conversion is the problem. A B-tree index on phone is sorted as strings, by the column's collation. The string order and the numeric order are different:

string order:  '100', '20', '3'
numeric order:   3,    20,  100

And many different strings convert to the same number: '4915112345678', '04915112345678', '4915112345678 ' and '4915112345678abc' all become 4915112345678 numerically. Since the index cannot tell MySQL where all those strings are, it has to look at every row. Hence type: ALL.

The opposite direction is fine

If the column is an integer and the value is a string, WHERE id = '42', MySQL converts the constant '42' once to a number and uses the index normally. The conversion happens on the constant, not on the column. The rule to remember: an index is usable when the column is left alone and only the constant is converted.

The same principle applies to functions on the column: WHERE LOWER(email) = ? or WHERE DATE(created_at) = ? stop a normal index on that column from being used, because the index stores the raw values.

Collation mismatches do it too

Warning 1739 mentions collation as well. Joining a utf8mb4_0900_ai_ci column with a utf8mb4_general_ci column, or with a latin1 column, can force a conversion on one side and disable the index in the same way. We found one such join in a reporting query during the same investigation.

Environment

1. Bind strings for string columns

The direct fix was removing the cast:

$digits = preg_replace('/\D+/', '', $input);
$customer = $repo->findOneBy(['phone' => $digits]);   // string, matches the column type

We also typed the repository method so the mistake cannot come back:

public function findByPhone(string $phoneDigits): ?Customer

2. Store phone numbers as strings, in one format

Some people suggested changing the column to BIGINT instead. That would have fixed the index use and created other bugs: leading zeros disappear, international prefixes like + cannot be stored, and some numbers exceed what fits comfortably. Phone numbers are identifiers made of digits, not quantities. We kept VARCHAR and normalised all stored numbers to E.164 format (+ followed by country code and number), in the application and with a check constraint:

ALTER TABLE customers
  ADD CONSTRAINT chk_phone_e164 CHECK (phone REGEXP '^\\+[1-9][0-9]{6,14}$');

MySQL enforces CHECK constraints since 8.0.16. The search input is normalised to the same format before the query, so lookups always compare equal strings.

3. Fix the collation mismatch in the report

For the reporting join, we converted the older table's column to the same character set and collation as the rest of the schema, during a maintenance window, with ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci.

1. Plan check for the real statement. We captured the exact SQL the ORM now generates, including the bound parameter type, from the general log on staging:

Execute  SELECT t0.id, t0.name, t0.email FROM customers t0 WHERE t0.phone = '+4915112345678' LIMIT 1

EXPLAIN on it shows type: ref, rows: 1.

2. Production timing. After the deploy, the slow log entries stopped. From performance_schema:

SELECT DIGEST_TEXT, COUNT_STAR, ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_ms, SUM_ROWS_EXAMINED/COUNT_STAR AS rows_per_call
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE 'SELECT %customers% phone = ?%';

| SELECT ... WHERE `t0` . `phone` = ? LIMIT ? | 18422 | 0.41 | 1.0000 |

Average 0.41 ms and one row examined per call, compared with about 3,100 ms and 8.2 million rows before.

3. A guard in CI. Our integration tests run with a check that, for a list of critical queries, runs EXPLAIN FORMAT=JSON and fails if access_type is ALL on a table with more than 10,000 rows in the test fixture. It caught a similar regression two months later, a WHERE order_ref = 1234 in a new report.

4. Monitoring. We alert on statement digests where rows examined per call jumps by 100 times compared to the previous week, which catches lost indexes regardless of the cause.

5. Security implications. None directly, but the numeric comparison had a correctness side effect worth knowing: because '4915112345678abc' converts to the same number, the integer query could match rows with malformed phone values that should not have matched at all. The check constraint now prevents such values from being stored.

Takeaways

• Comparing a string column with a number makes MySQL convert every row, so the index is skipped.
• Converting the constant is fine; converting the column (by type, collation or function) is not.
• EXPLAIN with possible_keys set but key NULL, plus warning 1739, is the signature.
• Store identifiers made of digits as strings, in one normalised format, and bind them as strings.

References: MySQL type conversion in expression evaluation, MySQL EXPLAIN output format, MySQL CHECK constraints.