MySQL : The ARCHIVE storage engine

The ARCHIVE storage engine

Added in MySQL 4.1.3, the archive storage engine lives up to its name by storing large amounts of data without taking up too much space. It too makes no use of any sort of indexing, and there are no means to repair the table should it become corrupted during a crash. To enable this storage engine, use the -with-archive-storage-engine configure option when building MySQL.

mysql> CREATE TABLE ktpot (firstname CHAR(30), surname CHAR(40), age INT) ENGINE = ARCHIVE;

This, as always, creates a .frm definition file, as well as .ARZ and .ARM data and metadata files.

Being an archive, you cannot DELETE, UPDATE or REPLACE records - you can only INSERT and SELECT. Again, with no indexes, the SELECT needs to perform a complete table scan. Although the records are compressed upon insertion, OPTIMIZE TABLE can compress the entire dataset even further. A .ARN file will temporarily appear when this occurs.

mysql> INSERT INTO ktpot VALUES('Quinton','Baxter','75');

mysql> SELECT * FROM ktpot ;

+-----------+------------+-----+

| firstname | surname    | age |

+-----------+------------+-----+

| ktpot      | cms           |  75 |

+-----------+------------+-----+