How do you clear service data (Data_free) in a MEMORY table in MySQL?

Question

I have a MEMORY table in MySQL for real-time chat (maybe this is not the best table type for this?) And deleting rows every night to keep chat logs driven by overhead reasons in the table. However, since you cannot run OPTIMIZE in the MEMORY table, how do you get rid of the overhead ( Data_free in show table status )?

Answer 1

How do you get rid of overhead?

You can force the table, using the MEMORY / HEAP storage engine, to recover the remaining space lost from deleted rows with ALTERing, but without changing anything. eg.

ALTER TABLE my_table ENGINE=MEMORY;

He will rewrite the table. Backup with a quote from the documentation :

To free memory used by rows that were deleted, use ALTER TABLE ENGINE = MEMORY to force the table to be restored.

EDIT

Perhaps this is not the best table type for this?

This does not look like the perfect MEMORY table application, in my opinion - by and large, I consider this an outdated engine. Some food for thought

Firstly, MEMORY tables cannot / do not use b-tree indexes (only for hash indexes), therefore, queries that could otherwise use the index for ORDERing or ranking (i.e., <,> operations) included manual sorting / filtering / exhaustive.

Secondly, Innodb tables will be in RAM if your innodb_buffer_pool is large enough and works better with parallel threads, so it often works so well, if not better, than the MEMORY table for most applications.

Third, perhaps most importantly, if your MySQL is ever disabled, you will lose all the data in your table. There are also consequences of truncating a table if you use replication.