Even given the present availability of low-cost, high-volume storage, organizations such as banks, hospitals, and retailers must take special care to store often enormous amounts of data in the most efficient way possible. Because this data typically must be maintained for long periods of time, even though it’s perhaps rarely accessed, it makes sense to compress it, uncompressing it only when necessary. Catering to such purposes, the ARCHIVE storage engine was added in version 4.1.3.

The ARCHIVE storage engine greatly compresses any data found in a table of this type, by using the zlib compression library (http://www.zlib.net/), and uncompress it on the fly as records are requested. In addition to selecting records, it’s also possible to insert records, as might be necessary when it becomes practical to migrate aging data over to an ARCHIVE table. However, it’s not possible to delete or update any data stored within these tables.

Note that any data stored in an ARCHIVE table will not be indexed, meaning SELECT operations can be rather inefficient. If for some reason you need to perform extended analysis an ARCHIVE table, it might make sense to convert the table to MyISAM and re-create necessary indexes.

Archive Engine Related FAQs