Trying MySQL MEMORY Tables as a Simple Cache (and Failing Spectacularly)
By Ercan - 26/10/2025
Sometimes you want a quick in-app cache without adding Redis or Hazelcast.
MySQL MEMORY tables look promising: “RAM-resident, fast, simple”.
Curious, I decided to test it myself on a local development environment.
Environment
- MySQL Server: 9.5.0 (Homebrew, default settings except max_heap_table_size)
- Local machine: MacBook Pro 15-inch, 2019, 2.3 GHz 8-Core Intel Core i9-9880H, 16 GB RAM
- MEMORY table max_heap_table_size: increased to 512 MB to accommodate 1 million rows
Test scenario:
- 50 parallel threads
- Insert 1 million OTP tokens
- 50,000 read queries
Benchmark Results
MEMORY Table
| Run | Insert Time (ms) | Insert Ops/ms | Query Time (ms) | Query Ops/ms |
|---|---|---|---|---|
| 1 | 123,370 | 8.11 | 874 | 57.21 |
| 2 | 123,991 | 8.07 | 910 | 54.95 |
| 3 | 121,105 | 8.26 | 905 | 55.25 |
InnoDB Table
| Run | Insert Time (ms) | Insert Ops/ms | Query Time (ms) | Query Ops/ms |
|---|---|---|---|---|
| 1 | 44,874 | 22.28 | 1,170 | 42.74 |
| 2 | 44,047 | 22.70 | 1,123 | 44.52 |
| 3 | 44,826 | 22.31 | 1,142 | 43.78 |
Expectation vs Reality
- Expectation: MEMORY tables in RAM = blazing fast inserts and reads.
- Reality:
- Insert throughput of MEMORY tables was slower than InnoDB!
- Read throughput was slightly better for MEMORY tables, but not dramatically.
Lessons Learned
- Concurrency Matters: MEMORY tables can slow down under multiple threads due to internal locking.
- InnoDB Optimizations: Insert buffer and MVCC give InnoDB an advantage for heavy insert workloads.
- Memory Limits: Don’t forget max_heap_table_size—even RAM has polite limits.
- Practical Takeaway: MEMORY tables are convenient for small, temporary datasets like OTP tokens or throttling data, but for medium-scale apps with high concurrency or durability needs, Redis or Hazelcast is still the safer choice.
Conclusion
- MySQL MEMORY tables aren’t always the magical RAM solution we hope for.
- Benchmarking is essential: theory doesn’t always match practice.
- Small experiments can save you big surprises in production environments.
👉 You can explore the full source code on GitHub:
https://github.com/ercansormaz/memory-vs-innodb-benchmark