# MySQL MEMORY Engine Cache
The MySQL `MEMORY` engine lets you keep the SQL interface while changing the storage behavior underneath. It stores table rows in RAM and is appropriate only for transient, non-critical data such as caches or temporary working sets.
## When This Is Useful
Use this pattern when:
- the source of truth is already MySQL,
- cache and source need the same schema,
- the same SQL query should work against cache and durable storage,
- Redis-style data structures are not needed,
- cache loss is acceptable.
This is a narrow but powerful tradeoff: **reduce data-model drift by making cache and database speak the same query language.**
## Architecture
```mermaid
flowchart LR
App[Application] --> Router[Shard/router]
Router --> C1[Cache C1<br/>MySQL MEMORY]
Router --> C2[Cache C2<br/>MySQL MEMORY]
Router --> C3[Cache C3<br/>MySQL MEMORY]
C1 -.miss.-> M1[Shard M1<br/>MySQL InnoDB]
C2 -.miss.-> M2[Shard M2<br/>MySQL InnoDB]
C3 -.miss.-> M3[Shard M3<br/>MySQL InnoDB]
```
Each durable shard can have a matching cache shard. The application can run the same query against cache first, then rerun it against the durable shard on miss.
## Why Not Just Tune InnoDB Buffer Pool?
The buffer pool caches pages for a durable InnoDB table. That helps performance, but it still preserves durable database semantics.
The `MEMORY` engine changes the contract:
| Choice | Contract |
|---|---|
| InnoDB + large buffer pool | durable table, memory-cached pages |
| MEMORY engine | RAM table, data lost on restart |
For a true disposable cache, losing rows on restart is a feature, not a bug.
## Write Pattern
The common pattern is cache-aside:
```text
read cache
if miss:
read durable shard
write same row/index representation into cache
return result
```
For writes, choose intentionally:
- write-through cache if read-after-write freshness matters,
- invalidate-on-write if stale cache is risky,
- TTL/lazy refresh if approximate freshness is fine.
## Decision Boundaries
| Requirement | MEMORY Engine Fit |
|---|---|
| same SQL query as source table | strong fit |
| set/list/sorted-set cache operations | weak fit |
| disposable read model | strong fit |
| cross-instance shared cache with mature clustering | weak fit |
| cache entries larger than RAM budget | weak fit |
The pattern is most useful when query reuse is more valuable than cache-specific features.
## Gotchas
- `MEMORY` rows disappear when MySQL restarts.
- The dataset must fit in RAM without causing swap.
- It is best for read-only or read-mostly workloads.
- It keeps MySQL operational overhead even though it is "just a cache."
- It does not replace Redis when you need rich cache structures, atomic scripts, streams, pub/sub, or mature cluster semantics.
## Senior-Level Check
Ask this before using it:
> Am I choosing MySQL MEMORY because it simplifies schema/query reuse, or because I am avoiding a proper cache design?
If the answer is schema reuse for a specific hot path, the pattern can be clean. If the answer is "we already know MySQL," it can become an awkward cache with database-shaped operational pain.
## Operational Checks
Set explicit memory limits, watch table size, and decide what happens on restart before production traffic depends on the cache. Treat warmup as part of the deployment plan: a cold MEMORY table can push all traffic to durable shards at once.
Also verify SQL behavior. Index choices, row formats, and engine limits differ from InnoDB. A query that is correct on both engines is useful; a query that quietly depends on different engine behavior is a maintenance trap.