A query that wraps a searched column in REPLACE(), LOWER(), DATE(), or another expression may no longer be able to use the ordinary B-tree index on the base value. The central problem is not merely the CPU cost of calling a function. The predicate now asks for a computed value that is not necessarily represented by the ordering of the existing index. On a large table, an apparently selective lookup can therefore become row-by-row evaluation. A sound correction starts with EXPLAIN, prefers a simpler predicate rewrite where possible, and uses an indexed generated column only when the normalization rule is stable enough to belong to the schema.

This guide addresses MySQL sargability, generated-column indexes, and execution-plan verification. Its scope is narrower than the site's existing EF Core query-performance guide and the separate article about INFORMATION_SCHEMA index metadata caching. The question here is whether a predicate can define an index search boundary, not whether an ORM translates a query correctly or whether a metadata view is fresh.

Direct answer: make the predicate searchable before adding indexes

Direct answer: Do not add another base-column index merely because EXPLAIN reports no chosen key. First identify whether the predicate computes a value from the searched column. Rewrite it as a direct range when possible; otherwise, index a stable generated expression and verify an exact expression-and-type match with representative data and EXPLAIN.

A sargable predicate can be turned into an index search argument. For example, phone = '5550100' provides a complete equality key. In contrast, REPLACE(phone, '.', '') = '5550100' asks for a value produced after transforming each stored value. If the table has only an index on phone, it does not automatically have an index ordered by REPLACE(phone, '.', '').

Sometimes the best solution is to normalize the input rather than the column. That works when stored values already follow one canonical format. It does not solve a historical data set in which separators remain inside stored values. In that case, a generated column can centralize the rule: derive a normalized string, index that string, and query either the generated column directly or an expression that MySQL can prove is identical.

The MySQL 8.4 manual confirms that generated columns can be indexed. It also explains an important optimizer feature: when a query expression matches the generated-column definition and has the same result type, the optimizer can substitute the generated column while constructing a plan. “Can” is deliberately conditional. Cost, cardinality, statistics, table size, and the fraction of qualifying rows still influence whether the index is selected. An index existing in the schema is not proof that a particular execution uses it.

Why a function on a column changes the search boundary

A B-tree stores sorted key values. Equality, range, and usable prefix predicates can identify a starting point and a bounded part of that tree. A predicate that must first evaluate a function for each candidate describes a different value domain. The ordering of the base values may not correspond to the ordering or equality classes of the computed values, and the base index does not necessarily contain the computed result.

This distinction explains why “the function is cheap” is an incomplete answer. A very cheap function multiplied by every row can dominate a point lookup, while a more expensive expression backed by a selective index may touch far fewer rows. The remedy is not to guess which function is expensive. It is to identify what value is indexed, what value the predicate requests, and whether the optimizer has a valid transformation between them.

Several patterns deserve different treatments:

  • WHERE DATE(created_at) = '2026-10-05' can often become a half-open range on created_at. That preserves the original column as the search argument and avoids a new schema object.
  • WHERE LOWER(email) = ... requires an explicit case-normalization and collation policy. Persisting or generating a normalized value may be suitable, but only after defining the comparison contract.
  • WHERE REPLACE(phone, '.', '') = ... may justify an indexed generated column when old rows contain different display formats and the normalization rule is stable.
  • WHERE amount + fee = ... should not receive an expression index automatically. It is worthwhile only if that derived value is a durable, selective access path whose read benefit offsets write and storage overhead.

A full scan is not always a bug. The MySQL manual identifies type=ALL as a full table scan but also notes that scans can be cheaper for very small tables, low-cardinality keys, or predicates that match much of the table. A fixture containing a handful of rows may therefore produce a scan even when an index is valid. A useful test needs enough data, a selective target, current statistics, and assertions that inspect possible_keys, the chosen key, access type, and result correctness together.

Four decisions before creating a generated-column index

The first decision is whether normalization is a data contract or a temporary workaround. Define exactly which dots, spaces, prefixes, case transformations, or punctuation are removed. If the rule changes next month, the generated expression and every matching query must change together. Normalization may also create collisions: two distinct raw strings can map to one normalized value. Before considering uniqueness, enumerate collisions, empty strings, and null semantics. The laboratory in this article uses a non-unique secondary index and makes no claim that normalized phone values are globally unique.

The second decision concerns type and collation. The official generated-column optimization rules require the expression and result type to match. Telephone identifiers should generally remain strings because leading zeroes may be meaningful. A conversion to an integer can silently change the contract. VARCHAR length, character set, and collation affect comparison behavior, so application parameters and the generated column must agree.

The third decision is whether a simpler rewrite is sufficient. Date extraction frequently becomes a range. Canonical values can sometimes be written at the service boundary and queried directly. A generated column is strongest when reads are frequent, the rule is stable, historical rows cannot be migrated in one safe step, and schema-level consistency is valuable. It should not be the default response to every function predicate.

The fourth decision is the write and storage budget. Every secondary index must be maintained during inserts, updates, and deletes. The generated expression also has a computation cost. Evaluate the read-to-write ratio, storage growth, buffer usage, and maintenance window with representative sandbox data. The source note records measurements from one environment, but those numbers cannot be generalized. This article intentionally offers no promised latency, speedup percentage, or row-count threshold.

Reproduction: compare a base index with a generated-column index

This reproduction uses a fixed MySQL 8.4.0 local container and disposable data. It first distinguishes a covering scan of the base index from equality lookup, then adds an indexed virtual generated column and verifies both the plan and result contract. These are reader-reproducible steps; not executed in the scheduler environment because Docker and a MySQL client were unavailable.

  1. Prerequisites: an operator-provisioned, network-isolated, empty MySQL 8.4.0 sandbox and an established MySQL client session. The complete SQL below tests the query; container provisioning and initialization are outside its scope. Run SELECT VERSION() separately and proceed only for 8.4.0. Stop if the version differs or the database name already exists; never clear existing data.
  2. Create contacts_lab, add an index on the raw value, and generate 10,000 deterministic fixture rows. Capture EXPLAIN FORMAT=JSON for the predicate that applies REPLACE() to phone_raw.
  3. Add phone_normalized as a virtual generated column with a secondary index. Explain a direct generated-column predicate and the exactly matching expression, then compare their row counts. Remove only the disposable container after reviewing the output.
SELECT VERSION();
CREATE DATABASE sargability_lab;
USE sargability_lab;
CREATE TABLE contacts_lab (
  id BIGINT NOT NULL PRIMARY KEY,
  phone_raw VARCHAR(32) NOT NULL,
  INDEX ix_phone_raw (phone_raw)
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO contacts_lab (id, phone_raw)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n, CONCAT('555.', LPAD(n, 7, '0')) FROM seq;

EXPLAIN FORMAT=JSON
SELECT id FROM contacts_lab
WHERE REPLACE(phone_raw, '.', '') = '5550004321';

ALTER TABLE contacts_lab
  ADD COLUMN phone_normalized VARCHAR(32)
    GENERATED ALWAYS AS (REPLACE(phone_raw, '.', '')) VIRTUAL,
  ADD INDEX ix_phone_normalized (phone_normalized);

EXPLAIN FORMAT=JSON
SELECT id FROM contacts_lab
WHERE phone_normalized = '5550004321';

EXPLAIN FORMAT=JSON
SELECT id FROM contacts_lab
WHERE REPLACE(phone_raw, '.', '') = '5550004321';

SELECT
  (SELECT COUNT(*) FROM contacts_lab
   WHERE REPLACE(phone_raw, '.', '') = '5550004321') AS function_count,
  (SELECT COUNT(*) FROM contacts_lab
   WHERE phone_normalized = '5550004321') AS generated_count;

Do not define success as a fixed number of milliseconds. The first plan can legitimately show access_type=index with key=ix_phone_raw: selecting only the primary key allows a covering full index scan. This is not an equality lookup on the normalized value. A baseline ALL scan is also possible; a nonempty key alone does not establish sargability. In the direct phone_normalized query, the chosen key should be ix_phone_normalized, and the access type should not be ALL. The optimizer may also match the identical REPLACE() expression to the generated-column index. If it does not, use SHOW WARNINGS after EXPLAIN, then inspect spelling, operand order, literals, result type, character set, and collation.

Both scalar count values must equal exactly 1; merely returning one output row is insufficient. A faster or more attractive plan is a failure if the result sets differ. Add fixtures for punctuation, empty strings, unmatched values, nullability if permitted, and normalization collisions before applying the pattern to another schema.

There is one practical issue in the script: MySQL's recursive common table expression has a recursion-depth limit. If the fixed server reports that the 10,000-row generator exceeds the configured limit, set cte_max_recursion_depth to a documented sandbox value for that session before the insert, or generate fixture rows with a cross join. Treat that adjustment as part of the fixture, not as an application tuning recommendation. The plan assertion matters more than the specific row generator.

Verification checklist and observable failure conditions

Verification must separate schema presence, plan selection, and semantic correctness. A test that checks only SHOW INDEX proves that an index definition exists; it does not prove that a query uses it. A test that checks only execution time is unstable under cache warmth and host load. A test that checks only the plan can miss a changed result contract. Use all three layers where appropriate, without converting one sandbox run into a performance promise.

First, verify the schema. SHOW CREATE TABLE contacts_lab must include a virtual generated column whose expression is the intended REPLACE(phone_raw, '.', ''), plus ix_phone_normalized. If the server rewrites formatting, compare the parsed meaning rather than whitespace.

Second, verify the plan. Capture JSON so automation can read access_type, key, and estimates without scraping a decorative table. The direct generated-column lookup must choose the expected index for the selective fixture. The function-on-base-column baseline must not be mislabeled as proof that all function predicates always scan; it proves only the behavior of this version, schema, distribution, and expression.

Third, verify semantics. Compare ordered primary-key sets, not only counts, when multiple matches are possible. Equal counts can still hide one removed row and one incorrect added row. The minimal fixture returns one row, but a regression suite should compare identifiers for canonical, punctuated, collision, empty, and absent inputs.

Fourth, verify failure behavior. Deliberately change the query to a nonmatching expression such as an additional TRIM() or an incompatible cast. The test should fail its expected-index assertion rather than silently accepting any plan. This negative control demonstrates that the harness can detect expression drift.

Fifth, preserve evidence without exposing private values. Store the MySQL version, table definition, redacted plan fields, fixture generator version, and assertion outcome. Do not copy connection strings, host addresses, user names, or real identifiers into an article or build log.

EXPLAIN versus EXPLAIN ANALYZE

EXPLAIN reports the optimizer's proposed execution plan. It is the safer first step because the target statement is not being used to collect runtime iterator measurements. EXPLAIN ANALYZE, by contrast, executes the query and reports actual timing, rows, and loops in tree format. That is stronger evidence, but it also incurs the query's real work. It is not a free inspection command for an unknown expensive statement.

Use an isolated data set or a controlled read-only copy before introducing EXPLAIN ANALYZE. Bound the data volume and runtime. Start with plain EXPLAIN FORMAT=JSON, inspect access paths, and only then collect runtime evidence when the cost is understood. The official documentation states that EXPLAIN ANALYZE uses tree output and includes actual execution information; that behavior is why it must be governed differently from an estimated plan.

A useful review answers at least five questions:

  1. Does possible_keys include the index expected from the predicate?
  2. Which index appears in key, rather than merely being available?
  3. Is access_type ALL, ref, range, or another method, and is that appropriate for the query?
  4. Do estimated and actual row counts diverge enough to suggest stale statistics or skew?
  5. Are the ordered result identifiers identical before and after the rewrite?

Plans are decisions made from a particular version, schema, data distribution, and statistics snapshot. A version update or distribution shift can legitimately change costs. Regression tests should therefore assert stable requirements, such as “the selective lookup must not use ALL,” “the expected index must be chosen,” and “the result set must match.” Avoid byte-for-byte snapshots of every cost estimate.

Deployment risks, DDL boundaries, and rollback

Adding a generated column and an index is a schema change. Even when version-specific documentation describes online DDL capabilities, “concurrent reads and writes are supported” does not mean zero metadata locking, zero I/O load, or instant completion for every table shape. Long transactions, large text columns, storage headroom, replication, and the exact server release can all affect behavior.

Do not copy an ALGORITHM or LOCK clause from a generic example and assume support. Test the exact alteration against the same MySQL release and a structurally representative disposable copy. A useful fail-closed technique is to request the intended algorithm explicitly in the sandbox; if the server cannot honor it, the alteration should fail instead of silently selecting a more disruptive path. The appropriate choice depends on the exact operation and version, so this article does not make a universal no-lock claim.

Use this decision checklist:

  • Semantics: enumerate collisions, leading zeroes, punctuation, empty strings, nulls, and malformed input.
  • Types: align parameter and generated-column string type, length, character set, and collation.
  • Plans: preserve before-and-after JSON plans and distinguish candidate indexes from the selected key.
  • Correctness: compare identifier sets across ordinary, punctuated, absent, and collision fixtures.
  • Writes: measure insert and update overhead in the sandbox instead of borrowing a third-party percentage.
  • DDL: test supported algorithm, lock behavior, duration, and storage requirements on the exact release.
  • Rollback: let the application return to the old query before removing the new index or column; do not bundle irreversible data cleanup with the same change.
  • Observation: monitor latency distribution, examined rows, errors, and database resource pressure rather than a single fastest sample.

A generated column also creates ownership questions. Schema migration code must define it consistently; ORM mappings should not attempt to write a virtual value; documentation must identify the normalization contract; and query authors need to know whether they should reference the generated name or an identical expression. Without that ownership, the schema can drift while tests continue to cover only one call site.

When not to use a generated column

Do not create one when a direct range rewrite solves the predicate clearly. Do not create one for an unstable business rule that changes frequently. Do not create one when almost every row matches, because the optimizer may still choose a scan and the index will continue to impose write cost. Do not create one merely to make a synthetic benchmark look better.

A stored canonical value may be superior when all writes pass through a well-governed boundary and historical data can be migrated safely. It makes the normalized contract explicit to every database client. A generated value is attractive when the database must deterministically derive it from existing columns and prevent clients from disagreeing about the formula. Neither choice eliminates the need for collision analysis or result verification.

Functional indexes are another design option in MySQL, but generated columns remain valuable when the derived value should be visible, inspectable, and directly queryable. The choice should consider maintainability and schema clarity, not only syntax length. Whichever representation is selected, record the exact expression and type as part of the contract.

Conclusion: prove index access instead of trusting index existence

MySQL query performance depends on whether a predicate maps to an ordered access path, not on how many indexes appear in the schema. When a function wraps a searched column, first look for a direct range or canonical-input rewrite. If historical values require stable normalization, an indexed generated column can make that computed value searchable.

The acceptance standard has three parts: the optimizer selects an appropriate access path for representative data, the rewritten query returns the same identifiers, and the schema change has bounded write, DDL, and rollback risk. This evidence-driven approach avoids two common errors: treating every full scan as a defect, and assuming that creating an index guarantees its use. Sargability is a query contract that must be demonstrated, not a slogan attached to a DDL statement.

Expected results and official references

These are acceptance conditions, not observed output from this run. A baseline seek or range path requires investigating fixture prerequisites and retaining the plan, not assuming that any nonempty key proves an efficient lookup.

Server version: 8.4.0
Baseline: access_type=index or ALL; key may be ix_phone_raw
Direct generated-column lookup: key=ix_phone_normalized; access_type=ref
function_count: 1
generated_count: 1
Failure: SQL error, wrong version, count mismatch, or direct lookup lacks ref access

After reviewing the evidence, stop and remove only the container created for this run. Confirm its name and creation time before cleanup. Do not remove a container that existed before this experiment.