MariaDB Generated Columns: Drop the Lookup Table

I deleted a lookup table last month and the app got faster. No migration of existing data, no backfill job, no caching layer — just a generated column and an index on top of it. It's one of those features that's been sitting in MariaDB for years and I keep watching people reinvent worse solutions around it.

What Problem This Actually Solves

You have a column with values you keep querying in a derived form. Classic examples: a phone column stored as (206) 555-0199 but you query it stripped of formatting. An email column you need to match case-insensitively. A JSON blob where you filter on one nested field constantly. A datetime where you repeatedly WHERE DATE(created_at) = ? and wonder why the index won't fire.

The usual answers are: add a separate normalized column and keep it in sync with triggers or application code, build a lookup/shadow table, or lean on a functional index (which MariaDB supports but with gotchas). Generated columns are cleaner than all of those. The database owns the derivation. You can't forget to update it. You can index it. You pay nothing at read time.

There are two flavors: VIRTUAL (computed on read, stored nowhere) and STORED (computed on write, persisted to disk). VIRTUAL saves space. STORED lets you index it on engines that don't support indexing virtual columns — and on InnoDB, you can actually index virtual generated columns directly, which is the sweet spot I reach for most.

A Real Scenario

I had a print management client with a jobs table. Orders come in with customer-supplied phone numbers in every format imaginable: 206-555-0199, (206) 555.0199, 2065550199, +1 206 555 0199. Staff search by phone number constantly. The original code was stripping non-digits in PHP before querying, which meant the index on phone was useless — every search was a full table scan.

The "obvious" fix would be to add a phone_normalized column, write a migration, backfill it, update every INSERT/UPDATE in the codebase, and add a trigger for anything that bypasses the ORM. I've done that dance. It's tedious and it drifts.

Here's what I did instead:

ALTER TABLE jobs
  ADD COLUMN phone_digits VARCHAR(20)
    GENERATED ALWAYS AS (
      REGEXP_REPLACE(phone, '[^0-9]', '')
    ) VIRTUAL,
  ADD INDEX idx_phone_digits (phone_digits);

That's it. InnoDB indexes virtual generated columns fine. Now in Laravel:

// In the Job model
public function scopeByPhone(Builder $query, string $phone): Builder
{
    $digits = preg_replace('/[^0-9]/', '', $phone);
    return $query->where('phone_digits', $digits);
}

// Usage
$jobs = Job::byPhone('(206) 555-0199')->get();

The query hits the index. The normalization lives in one place — the schema. I don't touch INSERT or UPDATE anywhere. Staff can type any format and get a hit.

Another Case: Killing a Lookup Table

I had a healthcare client with a patients table and a separate patient_search table that a cron job rebuilt nightly. The whole point of patient_search was to store a concatenated, lowercased, stripped version of name + DOB + MRN for fast LIKE queries. The cron job existed because keeping it in sync in real time was considered "too complex."

Nightly rebuilds mean an 8-hour window where the data is stale. In healthcare, that's bad. Deleted patients still showing up in search, newly registered patients invisible until morning.

I dropped the lookup table and replaced it with:

ALTER TABLE patients
  ADD COLUMN search_vector TEXT
    GENERATED ALWAYS AS (
      LOWER(CONCAT_WS(' ',
        COALESCE(last_name, ''),
        COALESCE(first_name, ''),
        COALESCE(mrn, ''),
        DATE_FORMAT(dob, '%Y%m%d')
      ))
    ) STORED,
  ADD FULLTEXT INDEX ft_patient_search (search_vector);

STORED here because I want FULLTEXT on it, and FULLTEXT on a virtual column isn't supported. Small tradeoff — a little extra disk, but the column is maintained transactionally with the row. No cron. No stale data. No separate table to JOIN.

The Laravel query:

public function scopeSearch(Builder $query, string $term): Builder
{
    return $query->whereRaw(
        'MATCH(search_vector) AGAINST(? IN BOOLEAN MODE)',
        ['+' . implode('* +', explode(' ', trim($term))) . '*']
    );
}

Not glamorous, but it works. Sub-10ms on 400k rows.

The Gotchas That Will Bite You

1. The expression must be deterministic. You can't use NOW(), RAND(), UUID(), or subqueries. If you try, MariaDB will tell you. This is a feature, not a limitation — generated columns shouldn't have side effects.

2. VIRTUAL columns can't be referenced in FOREIGN KEY constraints. This one catches people. If you're trying to use a generated column as part of a FK, reach for STORED instead — and even then, verify your MariaDB version supports it for your use case.

3. InnoDB can index VIRTUAL columns; MyISAM cannot. You're on InnoDB. You should be on InnoDB. But if you've got a legacy MyISAM table for some reason, switch to STORED before adding the index.

4. The expression runs at ALTER TABLE time for STORED columns. If you're adding a STORED generated column to a table with 10 million rows, that ALTER locks the table while it computes and writes the values. Use pt-online-schema-change or MariaDB's online DDL carefully. VIRTUAL has no such cost since nothing is written.

5. Your ORM will try to INSERT into them. This one actually stung me. Older versions of Laravel's query builder, when doing something like $model->fill($request->all()), would include the generated column in the INSERT statement if it showed up in $fillable. MariaDB rejects that with an error like The value specified for generated column 'phone_digits' is not allowed. Guard your generated columns in $guarded or just leave them out of $fillable entirely.

// In your model
protected $guarded = ['id', 'phone_digits', 'search_vector'];

6. REGEXP_REPLACE availability. REGEXP_REPLACE arrived in MariaDB 10.0.5. If you're somehow still on something older than that, use a chain of REPLACE() calls instead. Ugly, but it works.

7. Generated columns in WHERE need to match exactly. The optimizer will use the index on a generated column only when your WHERE clause matches what the column expression produces. If the column does LOWER(email) and you query WHERE email_lower = LOWER(?) — that works. If you do WHERE LOWER(email) = LOWER(?) on the base column — that doesn't use the generated column's index. Write your queries against the generated column itself.

When I'd Reach for This

• Phone/email normalization for search — almost always my first move now.
• Extracting a frequently-filtered field from a JSON column. JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.account_id')) as a VIRTUAL column with an index is way cleaner than scanning JSON on every row.
• Date-part filtering: YEAR(created_at), DATE(created_at). Kills the "why isn't my index being used" confusion dead.
• Any shadow/lookup table that a cron job maintains. If the purpose of that table is just a transformed view of another table's data, a generated column probably replaces it.
• Composite search strings like the patient example — anything where you're concatenating fields to build a searchable blob.

When I Wouldn't

If the derived value comes from another table — a JOIN, a subquery, an aggregate — generated columns can't help you there. That's a materialized view problem (MariaDB doesn't have native materialized views, so that's a topic for another day).

If the transformation is complex business logic that changes — like a pricing formula that gets tweaked — I'd keep that in the application layer. Schema changes to update the expression are fine technically but operationally annoying when product changes the formula every quarter.

And if you need the generated column to participate in replication-sensitive operations or you're running Galera Cluster, test your specific version. VIRTUAL columns and Galera have had some edge cases over the years, mostly around node resync. STORED is safer in that environment.

Closing

Generated columns are one of those features that makes me feel like I've been doing extra work for years for no reason. Every time I delete a lookup table or kill a cron sync job, I get a little annoyed that I didn't do it sooner. The implementation cost is one ALTER statement. The maintenance cost is zero. That math works for me.