MENU
ARCHIVE
The ARCHIVE storage engine produces special-purpose tables that store large amounts of unindexed data in a very small footprint. Rows are compressed as they are inserted, and uncompressed on the fly when retrieved. See Storage Engines for how it compares to the others.ARCHIVE supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE – making it suitable for logging or audit-trail data that is written once and never modified. It supports ORDER BY operations, BLOB columns, and essentially all data types except spatial ones. It uses row-level locking, and supports the AUTO_INCREMENT column attribute.
| Feature | ARCHIVE |
| Storage limits | None |
| Transactions | No |
| Locking granularity | Row |
| MVCC | No |
| Geospatial data type support | No |
| Geospatial indexing support | No |
| B-tree indexes | No |
| T-tree indexes | No |
| Hash indexes | No |
| Full-text search indexes | No |
| Clustered indexes | No |
| Data caches | No |
| Index caches | No |
| Compressed data | Yes |
| Encrypted data | Yes |
| Cluster database support | No |
| Replication support | Yes |
| Foreign key support | No |
| Backup / point-in-time recovery | Yes |
| Query cache support | Yes |
| Update statistics for data dictionary | Yes |
CREATE TABLE access_log (
id INT AUTO_INCREMENT PRIMARY KEY,
request_path VARCHAR(255),
logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = ARCHIVE;
INSERT INTO access_log (request_path) VALUES ('/index.html'), ('/api/orders');
-- SELECT and ORDER BY are supported; UPDATE/DELETE/REPLACE are not
SELECT * FROM access_log ORDER BY logged_at DESC;
-- UPDATE access_log SET request_path = '/home' WHERE id = 1; -- rejected