{"id":12321,"date":"2023-11-12T19:01:58","date_gmt":"2023-11-13T02:01:58","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12321"},"modified":"2023-11-12T21:57:51","modified_gmt":"2023-11-13T04:57:51","slug":"mysql-archive-storage-engine","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-archive-storage-engine\/","title":{"rendered":"MySQL ARCHIVE Storage Engine"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL ARCHIVE<\/code> storage engine to store large amounts of data with a very small footprint.<\/p>\n\n\n\n

Introduction to MySQL ARCHIVE storage engine<\/h2>\n\n\n\n

The ARCHIVE<\/code> storage engine allows you to create tables<\/a> that can store large amounts of unindexed data with minimal storage space usage.<\/p>\n\n\n\n

The ARCHIVE<\/code> storage engine can achieve that by not creating traditional indexes<\/a> and using compression techniques to reduce the amount of space needed to store the data.<\/p>\n\n\n\n

To check if the ARCHIVE<\/code> storage is available, you can use the SHOW<\/code> ENGINES<\/code> statement as follows:<\/p>\n\n\n

SHOW<\/span> ENGINES<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+\n| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |\n+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+\n| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |\n| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |\n| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |\n| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |\n| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |\n| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |\n| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |\n| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |\n| BLACKHOLE          | YES     | \/dev\/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |\n| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |\n| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |\n+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+\n11 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
To create a table that uses the ARCHIVE<\/code> storage engine, you set the ENGINE<\/code> clause to ARCHIVE<\/code> as follows:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name(\n   column_list\n) ENGINE<\/span>=ARCHIVE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you create an ARCHIVE<\/code> table, the storage engine generates files with the same name as the table.<\/p>\n\n\n\n
When working with ARCHIVE<\/code> tables, you can perform INSERT<\/code>, REPLACE<\/code>, and SELECT<\/code>, but not DELETE<\/code> or UPDATE<\/code>. Additionally, you can sort the rows using the ORDER<\/code> BY<\/code> clause.<\/p>\n\n\n\n
Even though you can use the AUTO_INCREMENT<\/code> column for the ARCHIVE<\/code> 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.<\/p>\n\n\n\n
When you insert a row into an ARCHIVE<\/code> table, the storage engine compresses it using the Zlib lossless data compression (zlib.net<\/code>).<\/p>\n\n\n\n
As you retrieve data from an ARCHIVE<\/code> table, the storage engine uncompresses the rows on demand. Also, it performs a complete table scan because it does not support a row cache.<\/p>\n\n\n\n
MySQL ARCHIVE storage engine example<\/h2>\n\n\n\n
We’ll create an ARCHIVE<\/code> table that stores data from the tables in the sample database<\/a>.<\/p>\n\n\n\n
First, create<\/a> an ARCHIVE<\/code> table called sales<\/code> that stores data from the orders<\/code>, orderdetails<\/code>, products<\/code>, and customers<\/code> tables:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales(\n  orderNumber INT<\/span> NOT<\/span> NULL<\/span>, \n  orderDate DATE<\/span>, \n  requiredDate DATE<\/span>, \n  status<\/span> VARCHAR<\/span>(15<\/span>), \n  productCode VARCHAR<\/span>(15<\/span>) NOT<\/span> NULL<\/span>, \n  quantityOrdered INT<\/span> NOT<\/span> NULL<\/span>, \n  priceEach DECIMAL<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>, \n  orderLineNumber SMALLINT<\/span> NOT<\/span> NULL<\/span>, \n  productName VARCHAR<\/span>(70<\/span>) NOT<\/span> NULL<\/span>, \n  productline VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>, \n  buyPrice DECIMAL<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>, \n  msrp DECIMAL<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>, \n  customerNumber INT<\/span> NOT<\/span> NULL<\/span>, \n  customerName VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>, \n  phone VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>, \n  addressLine1 VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>, \n  addressLine2 VARCHAR<\/span>(50<\/span>), \n  city VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>, \n  state VARCHAR<\/span>(50<\/span>), \n  country VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>\n) ENGINE<\/span> = ARCHIVE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> sales \nSELECT<\/span> \n  o.orderNumber, \n  o.orderDate, \n  o.requiredDate, \n  o.status, \n  d.productCode, \n  d.quantityOrdered, \n  d.priceEach, \n  d.orderLineNumber, \n  p.productName, \n  p.productline, \n  p.buyPrice, \n  p.msrp, \n  c.customerNumber, \n  c.customerName, \n  c.phone, \n  c.addressLine1, \n  c.addressLine2, \n  c.city, \n  c.state, \n  c.country \nFROM<\/span> \n  orders o \n  INNER<\/span> JOIN<\/span> orderDetails d using<\/span> (orderNumber) \n  INNER<\/span> JOIN<\/span> products p using<\/span> (productCode) \n  INNER<\/span> JOIN<\/span> customers c using<\/span> (customerNumber);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The query inserts 2996<\/code> rows into the sales ARCHIVE<\/code> table.<\/p>\n\n\n\n
Third, attempt to delete from the sales<\/code> table:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> \n  sales \nWHERE<\/span> \n  orderNumber = '10100'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issues the following error:<\/p>\n\n\n
ERROR 1031 (HY000): Table storage engine for 'sales' doesn't have this option<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
This is because the ARCHIVE<\/code> table doesn’t support the DELETE<\/code> operation.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use MySQL ARCHIVE<\/code> to store large amounts of data with a very small footprint.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, insert data<\/a> into the sales<\/code> table from the orders<\/code>, orderdetails<\/code>, customers<\/code>, and products<\/code> tables:<\/p>\n\n\n
In the ARCHIVE<\/code> table, you can use the AUTO_INCREMENT<\/a><\/code> column. The AUTO_INCREMENT<\/code> 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.<\/p>\n\n\n\n