The Ultimate Guide to High Availability with MariaDB

when archive can support index to speed up select peformance?

You are viewing an old version of this question. View the current version here.

Contents

  1. Installation
  2. Characteristics

The ARCHIVE Storage Engine is a storage engine that uses gzip to compress rows. It is mainly used for storing large amounts of data, without indexes, with only a very small footprint.

An ARCHIVE table is stored in two files on disk. There's a table definition file with an extension of .frm, and a data file with the extension .ARZ. At times during optimization, a .ARN file will appear.

New rows are inserted into a compression buffer and are flushed to disk when needed. SELECTs cause a flush. Sometimes, rows created by multi-row inserts are not visible until the statement is complete.

ARCHIVE allows a maximum of one key. The key must be on an AUTO_INCREMENT column, and can be a PRIMARY KEY or a non-unique key. However, it has a limitation: it is not possible to insert a value which is lower than the next AUTO_INCREMENT value.

Installation

The ARCHIVE Storage Engine was enabled by default until MariaDB 10.0. From MariaDB 10.1, the plugin needs to be specifically enabled, for example:

INSTALL SONAME 'ha_archive';

Characteristics

  • Supports INSERT and SELECT, but not DELETE, UPDATE or REPLACE.
  • Data is compressed with zlib as it is inserted, making it very small.
  • Data is slow the select, as it needs to be uncompressed, and, besides the query cache, there is no cache.
  • Supports AUTO_INCREMENT (since MariaDB/MySQL 5.1.6), which can be a unique or a non-unique index.
  • Since MariaDB/MySQL 5.1.6, selects scan past BLOB columns unless they are specifically requested, making these queries much more efficient.
  • Does not support spatial data types.
  • Does not support transactions.
  • Does not support foreign keys.
  • Does not support virtual columns.
  • No storage limit.
  • Supports row locking.
  • Supports table discovery, and the server can access ARCHIVE tables even if the corresponding .frm file is missing.
  • OPTIMIZE TABLE and REPAIR TABLE can be used to compress the table in its entirety, resulting in slightly better compression.
  • With MariaDB, it is possible to upgrade from the MySQL 5.0 format without having to dump the tables.
  • INSERT DELAYED is supported.
  • Running many SELECTs during the insertions can deteriorate the compression, unless only multi-rows INSERTs and INSERT DELAYED are used.

Answer

Indexes in the Archive tables are not currently on the roadmap.

You can use compression that other engines support: MyISAM, XtraDB and InnoDB, TokuDB.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.