The size of memory tables

I was doing some database sizing in MySQL 5.1.30 GA for memory tables. Generally I have used INFORMATION_SCHEMA.TABLES data_length,index_length as a reasonable guide.

However working with a MEMORY table, after deleting rows, the size did not decrease as expected. I deleted 10% of rows, and saw 0% reduction. This was confirmed by doing a subsequent ALTER where I saw the 10% reduction in memory size.

It requires more investigation, however I found these results unexpected and worthy of publishing.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.30    |
+-----------+


+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1111000 |             45 | 59.68744659 | 51.16348267 | 8.52396393 |


mysql> delete from location_ex4 limit 111000;
Query OK, 111000 rows affected (0.16 sec)


+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1000000 |             45 | 59.68744659 | 51.16348267 | 8.52396393 |


mysql> alter table location_ex4 engine=memory;
Query OK, 1000000 rows affected (2.95 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| table_name      | engine | row_format | table_rows | avg_row_length | total_mb    | data_mb     | index_mb   |
+-----------------+--------+------------+------------+----------------+-------------+-------------+------------+
| location_ex4    | MEMORY | Fixed      |    1000000 |             45 | 53.75530243 | 45.97259521 | 7.78270721