Every engineering team has that one meeting. Someone proudly announces,
"We reduced database traffic by 95% using Redis!"
Everyone smiles. Graphs look beautiful. CPU usage drops. Someone even starts preparing a "Performance Improvement" presentation.
Then...
Production throws an alert. A query that had been taking 9 seconds suddenly starts taking 62 seconds. Naturally, Redis becomes the prime suspect.
Except...
Redis wasn't the problem. It merely exposed a problem that had always existed.
The Background
We had an entity_attribute table that stored dynamic attributes.
| entity_id | key | value |
|---|---|---|
| 101 | STATUS | ACTIVE |
| 102 | TYPE | PREMIUM |
| 103 | STATUS | BLOCKED |
Almost every API fetched attributes by entity_id. Those lookups were extremely frequent.
To reduce database load, we cached the attributes in Redis. Instead of hitting MySQL thousands of times every minute, requests were served directly from memory.
The result?
- Lower database CPU ✅
- Lower latency ✅
- Happy developers ✅ Until one query reminded us that production always gets the last laugh.
The One Query That Couldn't Use Redis
Most lookups were by entity_id. But one API searched using the last few digits of the value. The query looked like this:
SELECT entity_id
FROM entity_attribute
WHERE value LIKE '%123';
Notice the leading wildcard. That tiny % completely changes how MySQL works.
Why LIKE '%123' Is Expensive
Indexes are like dictionaries. They help MySQL quickly locate values that start with something.
For example:
WHERE value LIKE 'ABC%'
can efficiently use an index.
But
WHERE value LIKE '%123'
asks MySQL:
"Find every value that ends with 123."
Since MySQL doesn't know where those values begin, it cannot efficiently traverse a normal B-tree index. Instead, it has to inspect a huge number of rows.
In other words...
The leading % quietly disables one of the database's biggest performance advantages.
Why Did It Become Worse After Redis?
Before Redis, this table was one of the hottest tables in MySQL. Thousands of reads kept its pages inside the InnoDB Buffer Pool. Even though the query performed a large scan, much of the data was already in memory.
The query was slow...
but tolerable.
Around 9 seconds.
After Redis, almost every lookup disappeared. MySQL gradually evicted those pages from the Buffer Pool.
Now the same scan had to fetch data from disk. Nothing about the SQL changed. Nothing about the indexes changed.
Only one thing changed. The table became cold.
The result?
9 seconds → 62 seconds.
Redis didn't slow MySQL. It simply stopped keeping the table warm.
The Investigation
At first, everyone blamed Redis. Then we looked deeper. Execution plans were nearly identical. The real difference was physical I/O.
The query wasn't CPU-bound anymore. It had become disk-bound. That's when we realized something important.
Even if we warmed the Buffer Pool again...
The query itself was fundamentally inefficient.
We needed a better query.
The Real Fix
Instead of searching using
WHERE value LIKE '%123'
We extracted the searchable suffix into a Generated Column.
For example:
ALTER TABLE entity_attribute
ADD COLUMN gen_val VARCHAR(3)
GENERATED ALWAYS AS (RIGHT(value,3)) STORED;
Then we indexed it.
CREATE INDEX idx_gen_val ON entity_attribute(gen_val);
Now the query became
SELECT entity_id FROM entity_attribute WHERE gen_val = '123';
No wildcard. No table scan. A simple indexed lookup.
Exactly what MySQL loves.
The Result
Instead of asking MySQL to inspect nearly every row...
We gave it a proper index. The query became dramatically faster.
More importantly...
Its performance was now independent of whether the table happened to be warm in the InnoDB Buffer Pool.
The optimization wasn't just faster. It was predictable.
Lessons Learned
Cache doesn't fix bad queries. It often hides them.
Lower database traffic changes memory behavior.
Redis reduced reads so effectively that MySQL no longer kept this table in the InnoDB Buffer Pool.
Leading wildcards are expensive.
LIKE '%123'
is one of those queries that should immediately make every DBA slightly uncomfortable.
Generated Columns are underrated.
They let you convert an expensive expression into something MySQL can index efficiently, without changing the original data.
Final Thoughts
Redis wasn't the villain. The Buffer Pool wasn't broken.
MySQL wasn't slow.
Our query was asking MySQL to do something it was never optimized to do.
Redis simply removed the accidental performance boost that had been masking the problem.
Sometimes production doesn't create bugs. It reveals assumptions. And in our case, the biggest optimization wasn't Redis.
It was replacing one innocent-looking % with an indexed generated column.
This may not be the perfect solution. Comment what could have been done better.
Top comments (0)