MySQL ARCHIVE Storage Engine

Summary: in this tutorial, you will learn how to use MySQL ARCHIVE storage engine to store large amounts of data with a very small footprint.

Introduction to MySQL ARCHIVE storage engine

The ARCHIVE storage engine allows you to create tables that can store large amounts of unindexed data with minimal storage space usage.

The ARCHIVE storage engine can achieve that by not creating traditional indexes and using compression techniques to reduce the amount of space needed to store the data.

To check if the ARCHIVE storage is available, you can use the SHOW ENGINES statement as follows:

SHOW ENGINES;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)Code language: plaintext (plaintext)

To create a table that uses the ARCHIVE storage engine, you set the ENGINE clause to ARCHIVE as follows:

CREATE TABLE table_name(
   column_list
) ENGINE=ARCHIVE;Code language: SQL (Structured Query Language) (sql)

When you create an ARCHIVE table, the storage engine generates files with the same name as the table.

When working with ARCHIVE tables, you can perform INSERT, REPLACE, and SELECT, but not DELETE or UPDATE. Additionally, you can sort the rows using the ORDER BY clause.

In the ARCHIVE table, you can use the AUTO_INCREMENT column. The AUTO_INCREMENT column can have either a unique or nonunique index. If you attempt to create an index on any other columns, you’ll get an error.

Even though you can use the AUTO_INCREMENT column for the ARCHIVE tables, you cannot insert a value into the column that is less than the current maximum column value. If you attempt to do so, you’ll get an error.

When you insert a row into an ARCHIVE table, the storage engine compresses it using the Zlib lossless data compression (zlib.net).

As you retrieve data from an ARCHIVE table, the storage engine uncompresses the rows on demand. Also, it performs a complete table scan because it does not support a row cache.

MySQL ARCHIVE storage engine example

We’ll create an ARCHIVE table that stores data from the tables in the sample database.

First, create an ARCHIVE table called sales that stores data from the orders, orderdetails, products, and customers tables:

CREATE TABLE sales(
  orderNumber INT NOT NULL, 
  orderDate DATE, 
  requiredDate DATE, 
  status VARCHAR(15), 
  productCode VARCHAR(15) NOT NULL, 
  quantityOrdered INT NOT NULL, 
  priceEach DECIMAL(10, 2) NOT NULL, 
  orderLineNumber SMALLINT NOT NULL, 
  productName VARCHAR(70) NOT NULL, 
  productline VARCHAR(50) NOT NULL, 
  buyPrice DECIMAL(10, 2) NOT NULL, 
  msrp DECIMAL(10, 2) NOT NULL, 
  customerNumber INT NOT NULL, 
  customerName VARCHAR(50) NOT NULL, 
  phone VARCHAR(50) NOT NULL, 
  addressLine1 VARCHAR(50) NOT NULL, 
  addressLine2 VARCHAR(50), 
  city VARCHAR(50) NOT NULL, 
  state VARCHAR(50), 
  country VARCHAR(50) NOT NULL
) ENGINE = ARCHIVE;Code language: SQL (Structured Query Language) (sql)

Second, insert data into the sales table from the orders, orderdetails, customers, and products tables:

INSERT INTO sales 
SELECT 
  o.orderNumber, 
  o.orderDate, 
  o.requiredDate, 
  o.status, 
  d.productCode, 
  d.quantityOrdered, 
  d.priceEach, 
  d.orderLineNumber, 
  p.productName, 
  p.productline, 
  p.buyPrice, 
  p.msrp, 
  c.customerNumber, 
  c.customerName, 
  c.phone, 
  c.addressLine1, 
  c.addressLine2, 
  c.city, 
  c.state, 
  c.country 
FROM 
  orders o 
  INNER JOIN orderDetails d using (orderNumber) 
  INNER JOIN products p using (productCode) 
  INNER JOIN customers c using (customerNumber);Code language: SQL (Structured Query Language) (sql)

The query inserts 2996 rows into the sales ARCHIVE table.

Third, attempt to delete from the sales table:

DELETE FROM 
  sales 
WHERE 
  orderNumber = '10100';Code language: SQL (Structured Query Language) (sql)

MySQL issues the following error:

ERROR 1031 (HY000): Table storage engine for 'sales' doesn't have this optionCode language: plaintext (plaintext)

This is because the ARCHIVE table doesn’t support the DELETE operation.

Summary

Use MySQL ARCHIVE to store large amounts of data with a very small footprint.

Was this tutorial helpful?