[SOLVED] Freeing DATA_FREE mysql HEAP tables?

matiasar · 11-01-2011, 11:50 AM

I run an application which uses several Memory tables (heap).
Sometimes a script deletes a lot of data from them. But if I do "SHOW TABLE STATUS" Data_free field still has a lot of bytes (in order of 200Mb indeed).

Is there any way to free that memory space without dropping and recreating those heap tables?
Because of the proprietary application I run in those server I had to stick with old mysql version 3.23 !

Thanks!

matiasar · 11-10-2011, 02:00 PM

I Finally found the answer to this "problem":

Code:

ALTER TABLE my_table ENGINE=MEMORY;

or if mysql server is 3.23:

Code:

ALTER TABLE my_table TYPE=HEAP;

These sentence force the memory table my_table to be recreated, and then data_free in show table status goes to zero. Memory allocated is freed.
Similar to OPTIMIZE TABLE for ordinary tables.