Search code examples
mysqloptimizationmyisam

How to get rid of the 1GB overhead of MyISAM table without locking too much the system

I had some maintenance task completed on one of the tables, and now it has 1GB of overhead.

Since the table operations run for hours (delete 40% of records, took 4 hours) I do not want to lock the database with the OPTIMIZE table command for hours, so I am looking for alternatives how to deal with this overhead and remove with best method.

The table itself is 3GB, having 204 705 records.

Solution

  • I was able to get around this problem by a 6 minute process doing the following:

    CREATE TABLE table_reduced LIKE table;
ALTER TABLE table_reduced DISABLE KEYS;

insert into table_reduced
SELECT 
    *
FROM
    table;

ALTER TABLE table_reduced ENABLE KEYS;

RENAME TABLE table TO table_old;
RENAME TABLE table_reduced TO table;

DROP TABLE `table_old`;