Section 8.8. The ARCHIVE Storage Engine

8.8. The `ARCHIVE` Storage Engine

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

The ARCHIVE storage engine is included in MySQL binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the --with-archive-storage-engine option.

To examine the source for the ARCHIVE engine, look in the sql directory of a MySQL source distribution.

You can check whether the ARCHIVE storage engine is available with this statement:

mysql>

SHOW VARIABLES LIKEhave_archive';

When you create an ARCHIVE table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine creates other files, all having names beginning with the table name. The data and metadata files have extensions of .ARZ and .ARM , respectively. An .ARN file may appear during optimization operations.

The ARCHIVE engine supports INSERT and SELECT , but not DELETE , REPLACE , or UPDATE . It does support ORDER BY operations, BLOB columns , and basically all but spatial data types. The ARCHIVE engine uses row-level locking.

Storage : Rows are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/ ). You can use OPTIMIZE TABLE to analyze the table and pack it into a smaller format (for a reason to use OPTIMIZE TABLE , see later in this section). Beginning with MySQL 5.0.15, the engine also supports CHECK TABLE . There are several types of insertions that are used:

An INSERT statement just pushes rows into a compression buffer, and that buffer flushes as necessary. The insertion into the buffer is protected by a lock. A SELECT forces a flush to occur, unless the only insertions that have come in were INSERT DELAYED (those flush as necessary).
A bulk insert is visible only after it completes, unless other inserts occur at the same time, in which case it can be seen partially. A SELECT never causes a flush of a bulk insert unless a normal insert occurs while it is loading.

Retrieval : On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used. To achieve better compression, you can use OPTIMIZE TABLE or REPAIR TABLE . The number of rows in ARCHIVE tables reported by SHOW TABLE STATUS is always accurate.

Additional resources

A forum dedicated to the ARCHIVE storage engine is available at http://forums.mysql.com/list.php?112 .

8.9. The `CSV` Storage Engine

The CSV storage engine stores data in text files using comma-separated values format.

To enable this storage engine, use the --with-csv-storage-engine option to configure when you build MySQL.

The CSV storage engine is included in MySQL-Max binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the --with-csv-storage-engine option.

To examine the source for the CSV engine, look in the sql/examples directory of a MySQL source distribution.

When you create a CSV table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in comma-separated values format.

mysql>

CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;

Query OK, 0 rows affected (0.12 sec)

mysql>

INSERT INTO test VALUES(1,'record one'),(2,'record two');

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

SELECT * FROM test;

+------+------------+
 i     c          
+------+------------+
  1    record one 
  2    record two 
+------+------------+
2 rows in set (0.00 sec)

If you examine the test.CSV file in the database directory created by executing the preceding statements, its contents should look like this: