MySQL Archive: Difference between revisions

Revision as of 01:59, 8 May 2012

Latest revision as of 00:47, 21 May 2024

(14 intermediate revisions by 12 users not shown)

Line 1:

{{Short description|Database engine}}

{{ Infobox Software

| name = ArchiveStorage Engine

Line 8 ⟶ 9:

| developer =

| released = 2004

| status = Active

| latest release version =

| latest release date =

Line 19:

| genre = [[Database engine]]

| license = [[GNU General Public License]]

| website = http://mysql.bkbits.com

}}

'''MySQL Archive''' is a storage engine for the [[MySQL]] [[relational database management system]]. Users can use this analytic storage engine to create a table that is “archive” only. Data cannot be deleted from this table, only added. The Archive engine uses a compression strategy based on the [[zlib]] library and it packs the rows using a bit header to represent nulls and removes all whitespace for character type fields. When completed, the row is inserted into the compression buffer and flushed to disk by an explicit flush table, a read, or the closing of the table.

'''MySQL Archive''' is a storage engine for the [[MySQL]] [[relational database management system]]. Users can use this analytic storage engine to create a table that is “archive” only. Data cannot be deleted from this table, only added. The Archive engine uses a compression strategy based on the [[zlib]] library and it packs the rows using a bit header to represent nulls and removes all whitespace for character type fields. When completed, the row is inserted into the compression buffer and flushed to disk by an explicit flush table, a read, or the closing of the table.

One of the current restrictions of Archive tables is that they do not support any indexes, so you will be looking at a table scan for any SELECT tasks. MySQL is examining index support for Archive tables in upcoming releases, but for now, one thing you can depend on to help table scan times is the fact that Archive tables are supported by the MySQL Query Cache, which can dramatically reduce response times for Archive table queries that are repetitively issued.<ref>http://dev.mysql.com/tech-resources/articles/storage-engine.html</ref>

One of the current restrictions of Archive tables is that they do not support any indexes, sothus you will be looking atnecessitating a table scan for any SELECT tasks. MySQL is examining index support for Archive tables in upcoming releases, but for nowhowever, one thing you can depend on to help table scan times is the fact that Archive tables are supported by the MySQL Query Cache, which can dramatically reduce response times for Archive table queries that are repetitively issued.<ref>https://web.archive.org/web/20100212100829/http://dev.mysql.com/tech-resources/articles/storage-engine.html The MySQL 5.0 Archive Storage Engine (archive date 20100212)</ref> MySQL is examining index support for Archive tables in upcoming releases.

The engine is not [[ACID]] compliant. Unlike [[OLTP]] engines, it uses a "stream" format to disk with no block boundaries. The head of the Archive file generated is a byte array representing the data format and contents of that file. In MySQL 5.1, a copy of the MySQL FRM file is stored in the header of each Archive file. The FRM file, which represents the definition of a table, allows an Archive file to be restored to a MySQL server if the Archive file is copied to the server.

Line 29 ⟶ 30:

Despite the use of [[zlib]], archive files are not compatible with [[gzio]], the basis of the [[gzip]] tools. It uses its own azio system that is a fork of gzio.

Archive differs from the other MySQL analytical engine, [[MyISAM]], by being a row -level locking engine and by keeping a constant version snapshot throughout a single query (making it [[Multiversion concurrency control|MVCC]]). This means that Archive does not lock for concurrent bulk inserts. For bulk inserts it performs an interlaced INSERT, so unlike MyISAM, order is not guaranteed.

Users can use the archive_reader tool to take an online snapshot of a table and to change the characteristics of an archive file.

Line 35 ⟶ 36:

To create an Archive table, specify the following engine string:

<sourcesyntaxhighlight lang="sqlmysql">

create table t1 (

a int,

b varchar(32))

ENGINE=ARCHIVE

</syntaxhighlight>

</source>

The MySQL <ref>[https://www.w3schools.blog/ MySQL Tutorial]</ref> Archive Storage Engine was authored and is maintained by [[Brian Aker]]. It was introduced in 2004 with MySQL 4.1.

The MySQL Archive Storage Engine was authored and is maintained by [[Brian Aker]]. It was introduced in 2004 with MySQL 4.1.

==References==

{{Reflist}}

== External links ==

* [http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html MySQL Documentation on Archive Storage Engine]

== External links ==

* [http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html MySQL Documentation on Archive Storage Engine]

{{database-software-stub}}

{{MySQL}}

Line 56 ⟶ 55:

[[Category:Database engines]]

[[Category:MySQL]]

{{database-software-stub}}