Difference in size of table between InnoDB and MEMORY engines

Question

Question

The same table stored on hard drive (InnoDB engine) takes 930.72 MB when in Ram (MEMORY engine) it takes 1538.54 MB.

• For what reason?
• How to calculate space needed to save data in memory? Are there any general rules?

---

Example

Text below is not question, but allow reconstruct my database state.

We create database:

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;

Create table innoDB

CREATE TABLE main(
 id INTEGER UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY,
 value INTEGER
);

Define procedure to load data

delimiter #
create procedure load_data(IN _max INTEGER)
begin
declare counter int unsigned default 0;
 truncate table main;
 start transaction;
 while counter < _max do
 set counter=counter+1;
 insert into main (value) values (counter);
 end while;
 commit;
end #
delimiter ;

Call procedure

call load_data(25000);

Create table MEMORY

-- SET max_heap_table_size = 16*1024*1024;
CREATE TABLE memory_main (
 id INTEGER UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY,
 value INTEGER
) ENGINE=MEMORY
AS SELECT * FROM main;

We measure size of both tables and this size is not the same.

SELECT
 table_name AS `Table`,
 round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "test"
 AND table_name LIKE "%main%";

• Rick James: The table has two INTs? And 25K rows? That's 0.2MB of data. Add on some overhead, and you still get less than 1MB. I can't believe you got on the order of 1GB of table space.
• Daniel: In My case I saved 25 M for test, but it takes a long time, so in this example I wrote 25 k.

Answer 1

InnoDB stores the data in a BTree, with the PRIMARY KEY clustered. Your 8 bytes of data will expand to an average of about 30 bytes due to record overhead, block overhead, BTree characteristics, etc, etc. The BTree blocks are 16KB each. After some point in the growth of the table, chunks of 8MB are allocated, so you will find the table growing is big increments.

MEMORY stores data in a Hash. So the PK is hashed to build the data structure. I am not familiar with the details, but they are significantly different than InnoDB's BTree structure. A common technique is to double the hash size when needed, thereby also leading to significant increments at seemingly random times.

What is the goal of this exercise?

• Daniel: Goal? I started learning of databases. This topic is slightly connected with two last questions, that you answered.
• Rick James: You might like Rick's RoTs, which gives a lot of MySQL-specific tips. Reading between the lines, or sometimes explicitly stated, are aspects of how MySQL works. Or they may lead to more questions, which I will probably answer.