MySQL : Saving a ton of space on old tables

Here is a nice article about using MySQL (> 5) archive engine.
The MySQL 5.0 Archive Storage Engine

If you have some tables that has mostly read-only for data retention purposes then you can do something like this to drastically reduce the disk spaced used by that table:

alter table MY_OLD_TABLE drop primary key, engine=archive;

note the archive table does not have primary key and dropping the primary key in this way prevents from rebuilding the table twice (compared to the case where you issue two alter table, one to drop key and second to switch engine.

One Response
  1. January 21, 2008