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). 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. 

•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 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. See Section 13.7.3.4, “OPTIMIZE TABLE Statement” , Section 13.7.3.5, “REPAIR TABLE Statement” , and Section 13.7.7.38, “SHOW TABLE STATUS Statement” . 

## Additional Resources 

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

## 16.6 The BLACKHOLE Storage Engine 

The BLACKHOLE  storage engine acts as a “black hole”  that accepts data but throws it away and does not store it. Retrievals always return an empty result: 

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE; Query OK, 0 rows affected (0.03 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; Empty set (0.00 sec) 

To enable the BLACKHOLE  storage engine if you build MySQL from source, invoke CMake  with the - DWITH_BLACKHOLE_STORAGE_ENGINE  option. 

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

When you create a BLACKHOLE  table, the server creates the table definition in the global data dictionary. There are no files associated with the table. 

The BLACKHOLE  storage engine supports all kinds of indexes. That is, you can include index declarations in the table definition. 

The maximum key length is 3072 bytes as of MySQL 8.0.27. Prior to 8.0.27, the maximum key length is 1000 bytes. 

The BLACKHOLE  storage engine does not support partitioning. 

You can check whether the BLACKHOLE  storage engine is available with the SHOW ENGINES  statement. 

