MySQL Generated Columns & Functional Indexes Prompt
Index expressions, extracted JSON fields, and computed predicates in MySQL 8.0 using generated columns and functional indexes.
- Target user
- Engineers and DBAs whose WHERE clauses wrap columns in functions and lose their indexes
- Difficulty
- Intermediate
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior MySQL 8.0 DBA who fixes queries that scan because a predicate wraps a column in a function. You know that a normal index on `col` is useless for `WHERE func(col) = ?`, and that MySQL 8.0 offers two fixes: a generated column (VIRTUAL or STORED) holding the expression, indexed normally; or a functional key part — an index built directly on an expression, e.g. `INDEX ((CAST(data->>'$.id' AS UNSIGNED)))`. You understand VIRTUAL (computed on read, no storage, can still be indexed) vs STORED (materialized on write, occupies space, rewrites the table to add). I will provide: - The slow query and its WHERE/ORDER BY/GROUP BY, especially any function around a column: [PASTE] - The table DDL and existing indexes (SHOW CREATE TABLE): [PASTE] - EXPLAIN / EXPLAIN ANALYZE output for the query today: [PASTE] - The MySQL version and rough table size / write rate: [DESCRIBE] Work through this: 1. **Find the non-sargable predicate.** Identify each place a function, CAST, JSON extraction, or arithmetic wraps an indexed column and explain why the optimizer can't use a plain index there. 2. **Choose the mechanism.** Recommend a functional index (`INDEX ((expr))`) when the expression is only used as a key, or a named generated column + index when the app or other queries should also read the computed value. Justify the choice. 3. **Pick VIRTUAL vs STORED.** Default to VIRTUAL — it costs no storage and can still be indexed; reserve STORED for cases where read-time recomputation is too expensive or the value must persist. Note that adding STORED rewrites the table. 4. **Match types exactly.** Make the expression in the index match the expression in the query character-for-character, including CAST target type, signedness, and collation, or the index won't be used. For JSON, wrap `->>` extraction in an explicit CAST. 5. **Verify the plan.** State the exact EXPLAIN change to expect (key chosen, rows dropped, "Using index" if covering). Output: the ALTER TABLE statements (generated column and/or functional index), the rewritten query if needed, and the before/after EXPLAIN expectation. Guardrails: build and EXPLAIN the new index on a replica or staging copy with realistic data first; back up the table before any ALTER, since adding a STORED generated column rewrites it and a functional index build can be heavy — schedule it in a window and prefer online DDL.
Why this prompt works
A huge fraction of “MySQL is slow” tickets come down to a single pattern: a predicate that wraps an indexed column in a function — DATE(created_at), LOWER(email), CAST(data->>'$.id' AS UNSIGNED) — which makes the predicate non-sargable and forces a scan even though an index exists. Before MySQL 8.0 the fix was awkward; now there are two clean tools, and this prompt makes the model pick deliberately between a functional index and a generated column rather than reaching for whichever it saw last.
The VIRTUAL-versus-STORED decision is where people quietly hurt themselves. STORED columns feel “real” and tempting, but they materialize data, consume storage, and rewrite the table when added. VIRTUAL columns cost nothing on disk, are computed on read, and — crucially — can still be indexed, which is exactly what most extraction-and-filter use cases need. The prompt defaults to VIRTUAL and demands a justification before STORED, so you only pay the rewrite cost when it genuinely buys something.
The detail that makes or breaks a functional index is exact expression matching: the indexed expression must match the query’s expression character-for-character, including CAST target type, signedness, and collation, or the optimizer simply ignores the index and you scan anyway. By forcing type-exact expressions and an explicit before/after EXPLAIN, plus replica testing and a backup before any table-rewriting ALTER, the prompt turns a fragile optimization into one you can verify actually fired.