Re: Data length jumps from 0 to 719016 on first insert!!!

[Ingo Strüwing]

Thank you. I can confirm this behaviour, though I get slightly smaller numbers.

I looked into the source (heap/hp_write.c) and found that memory is allocated in blocks (heap_write() -> next_free_record_pos() -> hp_get_new_block()).

You can check this yourself by adding more rows. You will see that data_length and index_length stay constant until the block is filled and another block is allocated.

The command SHOW TABLE STATUS LIKE 'sys_auth' says that you have fixed length records of 11 bytes each. There is a formula in the reference manual 14.3. The MEMORY (HEAP) Storage Engine, which shows how much memory a row takes.

The message of phpMyAdmin "ROW-SIZE: 1,638.4KB" is either very misunderstandable or just wrong. SHOW TABLE STATUS LIKE 'sys_auth' does not tell this. On the other hand, the latter tells "Data_free 0" after one or more inserts, which is also misunderstandable. It says how many data bytes come from deleted and not yet reused rows.