Mysql – Why does a MySQL MEMORY-backed table take so much more space than on disk

Try this:

$ ps -ef|grep [m]ysql

Identify the process id then

$ strace -cp <pid>

Leave it 10 seconds or a minute then ^C. That will tell you where the process is spending its time, e.g. it could just be waiting for the disk if you seen read and write dominate.

The exact memory requirement of a row is calculated from the following formula:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*)) 

[src]

ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

So, on a 64-bit machine, replace sizeof(char*) with 8.

You can get an estimate of the length_of_row from the Information Schema:

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM information_schema.tables WHERE  table_schema='foo' AND table_name='bar';

Then you add up all your BTREE keys and then HASH keys. Note that it might be worth it space-wise to convert any keys to HASH, as they require less memory.

I was going to mention the limitation of maximum MEMORY size dependent on max_heap_table_size, but gbn beat me to it.