Performance Comparison of MySQL MEMORY Engine vs InnoDB Using Large Test Data

First, a test table test_table is created using the InnoDB engine and populated with two million rows through a stored procedure that generates sequential names and integer values.

CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `value` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test_data`()
BEGIN
  DECLARE i INT DEFAULT 413241;
  WHILE i < 2000000 DO
    INSERT INTO test_table (name, value) VALUES (CONCAT('test_name_', i), i);
    SET i = i + 1;
  END WHILE;
END

CALL insert_test_data();

The create_time column is then filled with random timestamps using DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*1000000) SECOND) .

UPDATE test_table t
SET t.create_time = (
  SELECT DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*1000000) SECOND)
)
WHERE t.create_time IS NULL;

After the data load, an EXPLAIN ANALYZE SELECT * FROM test_table; query shows a full‑table scan taking about 3303 ms.

EXPLAIN ANALYZE SELECT * FROM test_table;

A duplicate table m_test_table is created with LIKE test_table and its storage engine switched to MEMORY.

CREATE TABLE m_test_table LIKE test_table;
ALTER TABLE m_test_table ENGINE=MEMORY;

Data is copied from the InnoDB table to the MEMORY table with an INSERT INTO ... SELECT statement. If the MEMORY table runs out of space, the global variables max_heap_table_size and tmp_table_size are increased (e.g., to 51 GB) to accommodate the data.

SET GLOBAL max_heap_table_size = 51539607552;
SET GLOBAL tmp_table_size = 51539607552;

Several benchmark experiments are performed:

Full‑table scan: MEMORY table query takes ~296 ms versus 3303 ms for InnoDB (≈11× faster).

Equality filter: MEMORY is about 3× faster.

Equality filter + COUNT(*): performance is nearly identical.

Multi‑condition filter: MEMORY again shows ~3× speedup.

IN clause with many values: ~3× faster.

LIKE with sorting: ~2× faster.

Additional queries display table size information and memory usage per storage engine.

SELECT * FROM information_schema.TABLES WHERE ... ORDER BY table_schema, table_name;

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,
       sys.format_bytes(SUM(current_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;

The article concludes that while the MEMORY engine offers significant speed advantages, it stores data only in RAM, so data is lost on server restart and it does not support transactions. Recommended use cases include caching, temporary tables, session data, read‑only data, and real‑time analytics.

To mitigate data‑loss risks, the article suggests keeping a synchronized InnoDB copy, using application‑level double writes, or employing triggers to keep both tables consistent.

Note: MEMORY does not support transactions; manual cleanup is required on errors.

Potential concerns such as why not use Redis, lock contention on frequent writes, and memory consumption estimates (e.g., 50 M rows occupying ~2 GB) are also discussed.