Indexing the Unindexable: MySQL Generated Columns and Functional Indexes

Every PHP developer hits this wall eventually. You have a perfectly good index on a column, you write a query that filters on that column, and EXPLAIN still shows a full table scan. The culprit is almost always a function wrapped around the column. The moment you write WHERE LOWER(email) = ? or WHERE DATE(created_at) = ?, MySQL can no longer use the plain index on email or created_at, because the index stores the raw values and the query asks about a transformed version of them.

For years the workaround was to denormalize: add a real column, populate it with the transformed value, keep it in sync with triggers or application code, and index that. It worked, but it was fragile. MySQL 8.0 gave us two cleaner tools, and they remain the right answer in the current 9.x LTS line. They are generated columns and functional indexes.

Generated Columns

A generated column is a column whose value is computed from an expression rather than written directly. You declare the expression once, and MySQL keeps the value correct forever. There are two flavors.

A VIRTUAL generated column is computed on read and stored nowhere. It costs nothing on disk and nothing on write, but it is recomputed every time it is accessed. A STORED generated column is computed on write and physically saved, so it costs disk space and a little write overhead, but reads are free. For indexing purposes the distinction matters less than you might think, because you can index both.

Here is a classic case. You store a price and a quantity, and you frequently query on the line total:

CREATE TABLE order_items (
    id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    unit_price  DECIMAL(10,2) NOT NULL,
    quantity    INT UNSIGNED  NOT NULL,
    line_total  DECIMAL(12,2) AS (unit_price * quantity) VIRTUAL,
    INDEX idx_line_total (line_total)
);

Now WHERE line_total > 500 uses idx_line_total directly. You never write line_total from your application, and it can never drift out of sync, because MySQL owns the calculation.

Indexing JSON, the Right Way

This is where generated columns earn their keep for modern PHP applications. JSON columns are wonderful for flexible, schemaless data, but you cannot put a normal index on a value buried inside a JSON document. A generated column pulls that value out into something indexable.

CREATE TABLE events (
    id       BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    payload  JSON NOT NULL,
    tenant_id INT UNSIGNED AS (
        JSON_UNQUOTE(JSON_EXTRACT(payload, '$.tenant_id'))
    ) STORED,
    INDEX idx_tenant (tenant_id)
);

A query like SELECT * FROM events WHERE tenant_id = 42 now uses the index instead of scanning and extracting from every row. I tend to reach for STORED here rather than VIRTUAL, because JSON extraction is comparatively expensive and you usually do not want to pay it on every read of a hot lookup column.

In Laravel, the migration reads naturally once you know the helper exists:

Schema::create('events', function (Blueprint $table) {
    $table->id();
    $table->json('payload');
    $table->unsignedInteger('tenant_id')
        ->storedAs("JSON_UNQUOTE(JSON_EXTRACT(payload, '$.tenant_id'))");
    $table->index('tenant_id');
});

Use virtualAs() for the virtual variant. Either way the column behaves like any other in your Eloquent models for reads, so the rest of your code does not need to know it is generated.

Functional Indexes

MySQL 8.0.13 added functional indexes, which let you skip the explicit generated column entirely and index an expression directly. Behind the scenes MySQL still creates a hidden generated column to back the index, but you no longer have to declare or name it.

CREATE TABLE users (
    id    BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    INDEX idx_email_lower ((LOWER(email)))
);

Note the double parentheses. The outer pair tells MySQL this is a functional key part rather than a plain column. With that index in place, WHERE LOWER(email) = '[email protected]' is an index lookup, which solves the case-insensitive search problem without a second stored column or a utf8mb4_*_ci collation change on the whole table.

The same approach fixes the date-truncation problem that catches everyone:

ALTER TABLE orders
    ADD INDEX idx_order_day ((CAST(created_at AS DATE)));

Now reporting queries that group or filter by calendar day can use the index instead of computing DATE(created_at) across the entire table.

The Gotchas

These features are powerful, but a few rules will save you debugging time.

The expression in your query must match the index definition exactly. MySQL compares the parsed expression, so LOWER(email) in the index and LOWER(email) in the query line up, but LOWER(TRIM(email)) will not use the LOWER(email) index. The optimizer is not doing algebra on your behalf, so write your queries to mirror your index definitions, and confirm with EXPLAIN rather than assuming.

Result type matters too. The generated value must have the same type the query produces, which is why an explicit CAST is often the safest way to define a date or numeric functional index. A subtle mismatch between a string and an integer can quietly defeat the index.

There are hard restrictions worth remembering. You cannot use a functional key part in a foreign key, you cannot reference a primary key column inside one, and only the functions that are allowed in generated columns are allowed here, which rules out anything non-deterministic. A column populated from NOW() or RAND() is a non-starter.

Finally, do not over-index. Every functional index is still a real index backed by a real, if hidden, column. It adds write cost and storage just like any other, so add them where EXPLAIN proves you have a problem, not preemptively across every expression you can imagine.

When to Reach for Which

If you query a computed value often and want it visible in your schema and your models, declare a generated column and index it. If you just need a query pattern to stop scanning and you do not care about exposing the intermediate value, a functional index is the lighter-touch option. Both have been stable since the 8.0 series and carry forward unchanged into the 9.x LTS releases, so there is no version risk in adopting them on any modern MySQL deployment. Either way, the goal is the same: stop wrapping indexed columns in functions at query time, and let the database index the shape of the data you actually search on.

Generated Columns

Indexing JSON, the Right Way

Functional Indexes

The Gotchas

When to Reach for Which

Sources