Go to the first, previous, next, last section, table of contents.
MySQL supports several storage engines that act as handlers for different
table types. MySQL storage engines include both those that handle
transaction-safe tables and those that handle non-transaction-safe tables:
-
The original storage engine was
ISAM, which managed non-transactional
tables. This engine has been replaced by MyISAM and should no longer
be used. It is deprecated in MySQL 4.1, and will be removed in MySQL 5.0.
-
In MySQL 3.23.0, the
MyISAM and HEAP storage engines were
introduced. MyISAM is an improved replacement for ISAM.
The HEAP storage engine provides in-memory tables.
The MERGE storage engine was
added in MySQL 3.23.25. It allows a collection of identical MyISAM
tables to be handled as a single table. All three of these storage engines
handle non-transactional tables, and all are included in MySQL by default.
Note that the
HEAP storage engine now
is known as the MEMORY engine.
-
The
InnoDB and BDB
storage engines that handle transaction-safe tables were introduced in later
versions of MySQL 3.23.
Both are available in source distributions as of MySQL 3.23.34a.
BDB is included in MySQL-Max binary distributions on those operating
systems that support it. InnoDB also is included in MySQL-Max binary
distributions for MySQL 3.23. Beginning with MySQL 4.0, InnoDB
is included by default in all MySQL binary distributions. In source
distributions, you can enable or disable either engine by configuring MySQL
as you like.
-
The
EXAMPLE storage engine was added in MySQL 4.1.3. It is a
``stub'' engine that does nothing. You can create tables with this engine,
but no data can be stored into them or retrieved from them. The purpose of
this engine is to serve as an example in the MySQL source code that
illustrates how to begin writing new storage engines. As such, it is
primarily of interest to developers.
-
NDB Cluster is the storage engine used by MySQL Cluster to implement
tables that are partitioned over many computers. It is available in source
code distributions as of MySQL 4.1.2 and binary distributions as of
MySQL-Max 4.1.3.
-
The
ARCHIVE storage engine was added in MySQL 4.1.3. It is used
for storing large amounts of data without indexes in a very small footprint.
-
The
CSV storage engine was added in MySQL 4.1.4. This engine stores
data in text files using comma-separated-values format.
-
The
FEDERATED storage engine was added in MySQL 5.0.3. This engine
stores data in a remote database. In this release, it works with MySQL only,
using the MySQL C Client API. Future releases will be able to connect to
other data sources using other driver or client connection methods.
This chapter describes each of the MySQL storage engines except for
InnoDB and NDB Cluster, which are covered in section 15 The InnoDB Storage Engine
and section 16 MySQL Cluster.
When you create a new table, you can tell MySQL what type of table to create
by adding an ENGINE or TYPE table option to the CREATE
TABLE statement:
CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;
ENGINE is the preferred term, but cannot be used before MySQL 4.0.18.
TYPE is available beginning with MySQL 3.23.0, the first
version of MySQL for which multiple storage engines were available.
If you omit the ENGINE or TYPE option, the default storage
engine is used. By default this is MyISAM. You can change it by
using the --default-storage-engine or --default-table-type
server startup option, or by setting the storage_engine or
table_type system variable.
When MySQL is installed on Windows using the MySQL Configuration Wizard,
the InnoDB storage engine will be the default instead of MyISAM.
See section 2.3.5.1 Introduction.
To convert a table from one type to another, use an ALTER TABLE
statement that indicates the new type:
ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;
See section 13.2.6 CREATE TABLE Syntax and
section 13.2.2 ALTER TABLE Syntax.
If you try to use a storage engine that is not compiled in or that is
compiled in but deactivated, MySQL instead creates a table of type
MyISAM. This behavior is convenient when you want to copy tables
between MySQL servers that support different storage engines. (For example, in
a replication setup, perhaps your
master server supports transactional storage engines for increased safety,
but the slave servers use only non-transactional storage engines for greater
speed.)
This automatic substitution of the MyISAM table type when an
unavailable type is specified can be confusing for new MySQL
users. In MySQL 4.1 and up, a warning is generated when a table type is
automatically changed.
MySQL always creates an `.frm' file to hold the table and column
definitions. The table's index and data may be stored in one or more other
files, depending on the table type.
The server creates the `.frm' file above the storage engine level.
Individual storage engines create any additional files required for the tables
that they manage.
A database may contain tables of different types.
Transaction-safe tables (TSTs) have several advantages over
non-transaction-safe tables (NTSTs):
-
Safer. Even if MySQL crashes or you get hardware problems, you
can get your data back, either by automatic recovery or from a backup
plus the transaction log.
-
You can combine many statements and accept them all at the same time with
the
COMMIT statement (if autocommit is disabled).
-
You can execute
ROLLBACK to ignore your changes (if
autocommit is disabled).
-
If an update fails, all your changes will be restored. (With
non-transaction-safe tables, all
changes that have taken place are permanent.)
-
Transaction-safe storage engines
can provide better concurrency for tables that get many updates concurrently
with reads.
Note that to use the InnoDB storage engine in MySQL 3.23, you
must configure at least the innodb_data_file_path startup option.
In 4.0 and up, InnoDB uses default configuration values if you specify
none.
See section 15.4 InnoDB Configuration.
Non-transaction-safe tables have several advantages of their own, all
of which occur because there is no transaction overhead:
-
Much faster
-
Lower disk space requirements
-
Less memory required to perform updates
You can combine transaction-safe and non-transaction-safe tables in the same statements to get the best
of both worlds. However, within a transaction with autocommit disabled,
changes to non-transaction-safe tables still are committed immediately and
cannot be rolled back.
MyISAM is the default storage engine as of MySQL 3.23. It is
based on the ISAM code but has many useful extensions.
Each MyISAM table is stored on disk in three files. The files have
names that begin with the table name and have an extension to indicate the
file type. An `.frm' file stores the table definition. The data file
has an `.MYD' (MYData) extension. The index file has an `.MYI'
(MYIndex) extension,
To specify explicitly that you want a MyISAM table, indicate that with
an ENGINE or TYPE table option:
CREATE TABLE t (i INT) ENGINE = MYISAM;
CREATE TABLE t (i INT) TYPE = MYISAM;
Normally, the ENGINE or TYPE option is unnecessary;
MyISAM is the default storage engine unless the default has been
changed.
You can check or repair MyISAM tables with the myisamchk
utility. See section 5.7.2.7 Using myisamchk for Crash Recovery. You can compress MyISAM tables with
myisampack to take up much less space.
See section 8.2 myisampack, the MySQL Compressed Read-only Table Generator.
The following characteristics of the MyISAM storage engine are
improvements over the older ISAM engine:
-
All data values are stored with the low byte first. This makes the data
machine and operating system independent. The only requirement for binary
portability is that the machine uses two's-complement signed integers
(as every machine for the last 20 years has) and IEEE floating-point
format (also totally dominant among mainstream machines). The only area of
machines that may not support binary compatibility are embedded systems,
which sometimes have peculiar processors.
There is no big speed penalty for storing data low byte first; the bytes
in a table row normally are unaligned and it doesn't take that much more
power to read an unaligned byte in order than in reverse order. Also, the
code in the server that fetches column values is not time critical compared
to other code.
-
Large files (up to 63-bit file length) are supported on
filesystems and operating systems that support large files.
-
Dynamic-sized rows are much less fragmented when mixing deletes
with updates and inserts. This is done by automatically combining adjacent
deleted blocks and by extending blocks if the next block is deleted.
-
The maximum number of indexes per table is 64 (32 before MySQL 4.1.2). This
can be changed by recompiling. The maximum number of columns per index is 16.
-
The maximum key length is 1000 bytes (500 before MySQL 4.1.2).
This can be changed by recompiling. For the case of a key longer than
250 bytes, a larger key block size than the default of 1024 bytes is used.
-
BLOB and TEXT columns can be indexed.
-
NULL values are allowed in indexed columns. This takes 0-1
bytes per key.
-
All numeric key values are stored with the high byte first to allow better index
compression.
-
Index files are usually much smaller with
MyISAM than with
ISAM. This means that MyISAM normally will use less
system resources than ISAM, but will need more CPU time when inserting
data into a compressed index.
-
When records are inserted in sorted order (as when you are using an
AUTO_INCREMENT column), the index tree is split so that the high
node only contains one key. This improves space utilization in the index
tree.
-
Internal handling of one
AUTO_INCREMENT column per table.
MyISAM automatically updates this column for INSERT/UPDATE. This
makes AUTO_INCREMENT columns faster (at least 10%).
Values at the top of the sequence are not reused after being deleted as they
are with ISAM. (When an AUTO_INCREMENT column is
defined as the last column of a multiple-column index, reuse of deleted
values does occur.) The AUTO_INCREMENT value can be reset with
ALTER TABLE or myisamchk.
-
If a table doesn't have free blocks in the middle of the data file, you can
INSERT new rows into it at the same time that other threads are
reading from the table. (These are known as concurrent inserts.) A free block can
occur as a result of deleting rows or an update of a dynamic length row with
more data than its current contents. When all free blocks are used up
(filled in), future inserts become concurrent again.
-
You can put the data file and index file on different directories
to get more speed with the
DATA DIRECTORY and INDEX DIRECTORY
table options to
CREATE TABLE. See section 13.2.6 CREATE TABLE Syntax.
-
As of MySQL 4.1, each character column can have a different character set.
-
There is a flag in the
MyISAM index file that indicates whether the
table was closed correctly. If mysqld is started with the
--myisam-recover option, MyISAM tables are automatically
checked when opened and repaired if the table wasn't closed
properly.
-
myisamchk marks tables as checked if you run it with the
--update-state option. myisamchk --fast checks only those
tables that don't have this mark.
-
myisamchk --analyze stores statistics for key parts, not only for
whole keys as in ISAM.
-
myisampack can pack BLOB and VARCHAR columns;
pack_isam cannot.
MyISAM also supports the following features, which MySQL
will be able to use in the near future:
-
Support for a true
VARCHAR type; a VARCHAR column starts
with a length stored in two bytes.
-
Tables with
VARCHAR may have fixed or dynamic record length.
-
VARCHAR and CHAR columns may be up to 64KB.
-
A hashed computed index can be used for
UNIQUE. This will allow
you to have UNIQUE on any combination of columns in a table. (You
can't search on a UNIQUE computed index, however.)
The following options to mysqld can be used to change the behavior of
MyISAM tables:
--myisam-recover=mode
-
Set the mode for automatic recovery of crashed
MyISAM tables.
--delay-key-write=ALL
-
Don't flush key buffers between writes for any
MyISAM table.
Note: If you do this, you should not use MyISAM tables from
another program (such as from another MySQL server or with myisamchk) when
the table is in use. Doing so will lead to index corruption.
Using --external-locking will not help for tables that use
--delay-key-write.
See section 5.2.1 mysqld Command-Line Options.
The following system variables affect the behavior of
MyISAM tables:
bulk_insert_buffer_size
-
The size of the tree cache used in bulk insert
optimization. Note: This is a limit per thread!
myisam_max_extra_sort_file_size
-
Used to help MySQL to decide when to use the slow but safe key cache index
creation method. Note: This parameter is given in megabytes
before MySQL 4.0.3, and in bytes as of 4.0.3.
myisam_max_sort_file_size
-
Don't use the fast sort index method to create an index if the temporary
file would become larger than this. Note: This parameter is
given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
myisam_sort_buffer_size
-
Set the size of the buffer used when recovering tables.
See section 5.2.3 Server System Variables.
Automatic recovery is activated if you start mysqld with the
--myisam-recover option. In this case, when the server opens a
MyISAM table, it checks whether the table
is marked as crashed or whether the open count variable for the
table is not 0 and you are running the server with
--skip-external-locking. If either of these conditions is true, the
following happens:
-
The table is checked for errors.
-
If the server finds an error, it tries to do a fast table repair (with
sorting and without re-creating the data file).
-
If the repair fails because of an error in the data file (for example, a
duplicate-key error), the server tries again, this time re-creating the
data file.
-
If the repair still fails, the server tries once more with the old repair option
method (write row by row without sorting). This method should be able to repair
any type of error and has low disk space requirements.
If the recovery wouldn't be able to recover all rows from a previous
completed statement and you didn't specify FORCE in the value of the
--myisam-recover option, automatic repair aborts with an error
message in the error log:
Error: Couldn't repair table: test.g00pages
If you specify FORCE, a warning like this is written instead:
Warning: Found 344 of 354 rows when repairing ./test/g00pages
Note that if the automatic recovery value includes BACKUP, the
recovery process creates files with names of the form
`tbl_name-datetime.BAK'. You should have a cron script that
automatically moves these files from the database directories to backup
media.
MyISAM tables use B-tree indexes. You can roughly calculate
the size for the index file as (key_length+4)/0.67, summed over
all keys. This is for the worst case when all keys are inserted in
sorted order and the table doesn't have any compressed keys.
String indexes are space compressed. If the first index part is a
string, it will also be prefix compressed. Space compression makes the
index file smaller than the worst-case figure if the string column has a lot
of trailing space or is a VARCHAR column that is not always used
to the full length. Prefix compression is used on keys that start
with a string. Prefix compression helps if there are many strings
with an identical prefix.
In MyISAM tables, you can also prefix compress numbers by specifying
PACK_KEYS=1 when you create the table. This helps when you have
many integer keys that have an identical prefix when the numbers are stored
high-byte first.
MyISAM supports three different storage formats. Two of them (fixed
and dynamic format) are chosen automatically depending on the type of
columns you are using. The third, compressed format, can be created only
with the myisampack utility.
When you CREATE or ALTER a table that has no BLOB
or TEXT columns, you can force the table format to FIXED or
DYNAMIC with the ROW_FORMAT table option. This causes
CHAR and VARCHAR columns to become CHAR for
FIXED format or VARCHAR for DYNAMIC format.
In the future, you will be able to compress or decompress tables by specifying
ROW_FORMAT={COMPRESSED | DEFAULT} to ALTER TABLE.
See section 13.2.6 CREATE TABLE Syntax.
Static format is the default for MyISAM tables. It is used when the
table contains no variable-length columns (VARCHAR, BLOB, or
TEXT). Each row is stored using a fixed number of bytes.
Of the three MyISAM storage formats, static format is the simplest
and most secure (least subject to corruption). It is also the fastest of the
on-disk formats. The speed comes from the easy way that rows in the data file
can be found on disk: When looking up a row based on a row number in the
index, multiply the row number by the row length. Also, when scanning a
table, it is very easy to read a constant number of records with each disk
read operation.
The security is evidenced if your computer crashes while the MySQL server is
writing to a fixed-format MyISAM file. In this case, myisamchk
can easily determine where each row starts and ends, so it can usually
reclaim all records except the partially written one. Note that MyISAM
table
indexes can always be reconstructed based on the data rows.
General characteristics of static format tables:
-
All
CHAR, NUMERIC, and DECIMAL columns are space-padded
to the column width.
-
Very quick.
-
Easy to cache.
-
Easy to reconstruct after a crash, because records are located in fixed
positions.
-
Reorganization is unnecessary unless you delete a huge number of records
and want to return free disk space to the operating system. To do this,
use
OPTIMIZE TABLE or myisamchk -r.
-
Usually require more disk space than for dynamic-format tables.
Dynamic storage format is used if a MyISAM table contains any
variable-length columns (VARCHAR, BLOB, or TEXT), or if
the table was created with the ROW_FORMAT=DYNAMIC option.
This format is a little more complex because each row has a header that
indicates
how long it is. One record can also end up at more than one location when it
is made longer as a result of an update.
You can use OPTIMIZE TABLE or myisamchk to defragment a
table. If you have fixed-length columns that you access or change frequently
in a table that also contains some variable-length columns, it might be
a good idea to move the variable-length columns to other tables just to
avoid fragmentation.
General characteristics of dynamic-format tables:
Compressed storage format is a read-only format that is generated with the
myisampack tool.
All MySQL distributions as of version 3.23.19 include myisampack by
default. (This version is when MySQL was placed under the GPL.) For earlier
versions, myisampack was included only with licenses or support
agreements, but the server still can read tables that were compressed
with myisampack. Compressed tables can be uncompressed with
myisamchk. (For the ISAM storage engine, compressed tables
can be created with pack_isam and uncompressed with isamchk.)
Compressed tables have the following characteristics:
-
Compressed tables take very little disk space. This minimizes disk usage, which
is very nice when using slow disks (such as CD-ROMs).
-
Each record is compressed separately, so there is very little access overhead. The
header for a record is fixed (1-3 bytes) depending on the biggest record in the
table. Each column is compressed differently. There is usually a different
Huffman tree for each column. Some of the compression types are:
-
Suffix space compression.
-
Prefix space compression.
-
Numbers with a value of zero are stored using one bit.
-
If values in an integer column have a small range, the column is stored
using the smallest possible type. For example, a
BIGINT column
(eight bytes) can be stored as a TINYINT column (one byte) if all
its values are in the range from -128 to 127.
-
If a column has only a small set of possible values, the column type is
converted to
ENUM.
-
A column may use a combination of the preceding compressions.
-
Can handle fixed-length or dynamic-length records.
The file format that MySQL uses to store data has been extensively
tested, but there are always circumstances that may cause database tables
to become corrupted.
Even though the MyISAM table format is very reliable (all changes to
a table made by an SQL statement are written before the statement returns),
you can still get corrupted tables if some of the following things happen:
-
The
mysqld process is killed in the middle of a write.
-
Unexpected computer shutdown occurs (for example, the computer is turned off).
-
Hardware errors.
-
You are using an external program (such as
myisamchk) on a table that
is being modified by the server at the same time.
-
A software bug in the MySQL or
MyISAM code.
Typical symptoms for a corrupt table are:
You can check whether a MyISAM table is okay with the CHECK
TABLE statement. You can repair a corrupted MyISAM table with
REPAIR TABLE. When mysqld is not running, you can also
check or repair a table with the myisamchk command.
See section 13.5.2.3 CHECK TABLE Syntax,
section 13.5.2.6 REPAIR TABLE Syntax, and section 5.7.2.1 myisamchk Invocation Syntax.
If your tables become corrupted frequently, you should try to determine why
this is happening. The most important thing to know is whether the table
became corrupted as a result of a server crash. You can verify this easily
by looking for a recent restarted mysqld message in the error log.
If there is such a message, it is likely that table corruption is a
result of the server dying. Otherwise, corruption may have occurred during
normal operation, which is a bug. You should try to create a reproducible
test case that demonstrates the problem.
See section A.4.2 What to Do If MySQL Keeps Crashing and section E.1.6 Making a Test Case If You Experience Table Corruption.
Each MyISAM index (`.MYI') file has a counter in the header
that can be used to check whether a table has been closed properly.
If you get the following warning from CHECK TABLE or myisamchk,
it means that this counter has gone out of sync:
clients are using or haven't closed the table properly
This warning doesn't necessarily mean that the table is corrupted, but
you should at least check the table to verify that it's okay.
The counter works as follows:
-
The first time a table is updated in MySQL, a counter in the
header of the index files is incremented.
-
The counter is not changed during further updates.
-
When the last instance of a table is closed (because of a
FLUSH
TABLES operation or because there isn't room in the table cache), the
counter is decremented if the table has been updated at any point.
-
When you repair the table or check the table and it is found to be okay,
the counter is reset to zero.
-
To avoid problems with interaction with other processes that might
check the table, the counter is not decremented on close if it was zero.
In other words, the counter can go out of sync only under these conditions:
-
The
MyISAM tables are copied without a preceding LOCK TABLES and
FLUSH TABLES.
-
MySQL has crashed between an update and the final close.
(Note that the table may still be okay, because MySQL always issues writes
for everything between each statement.)
-
A table was modified by
myisamchk --recover or myisamchk
--update-state at the same time that it was in use by mysqld.
-
Many
mysqld servers are using the table and one server performed a
REPAIR TABLE or CHECK TABLE on the table while it was in use by
another server. In this setup, it is safe to use CHECK TABLE,
although you might get the warning from other servers. However, REPAIR
TABLE should be avoided because when one server replaces the data file
with a new one, this is not signaled to the other servers.
In general, it is a bad idea to share a data directory among multiple servers.
See section 5.10 Running Multiple MySQL Servers on the Same Machine for additional discussion.
The MERGE storage engine was introduced in MySQL 3.23.25. It
is also known as the MRG_MyISAM engine. The code is now reasonably
stable.
A MERGE table is a collection of identical MyISAM tables that
can be used as one. ``Identical'' means that all tables have
identical column and index information. You can't merge tables in which the
columns are listed in a different order, don't have exactly the same columns, or
have the indexes in different order. However, any or all of the tables can be
compressed with myisampack.
See section 8.2 myisampack, the MySQL Compressed Read-only Table Generator.
Differences in table options such as AVG_ROW_LENGTH, MAX_ROWS,
or PACK_KEYS do not matter.
When you create a MERGE table, MySQL creates two files on disk.
The files have names that begin with the table name and have an extension
to indicate the file type. An `.frm' file stores the table definition,
and an `.MRG' file contains the names of the tables that should be
used as one. (Originally, all used tables had to be in the same database
as the MERGE table itself. This restriction has been lifted as of
MySQL 4.1.1.)
You can use SELECT, DELETE, UPDATE, and (as of MySQL
4.0) INSERT on the collection of tables. For the moment, you must
have SELECT, UPDATE, and DELETE privileges on the
tables that you map to a MERGE table.
If you DROP the MERGE table, you are dropping only the
MERGE specification. The underlying tables are not affected.
When you create a MERGE table, you must specify a
UNION=(list-of-tables) clause that indicates which tables you want to
use as one. You can optionally specify an INSERT_METHOD option if you
want inserts for the MERGE table to happen in the first or last table
of the UNION list. If you don't specify any INSERT_METHOD
option or specify it with a value of NO, attempts to insert records
into the MERGE table result in an error.
The following example shows how to create a MERGE table:
mysql> CREATE TABLE t1 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20));
mysql> CREATE TABLE t2 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20));
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
Note that the a column is indexed in the MERGE table, but is
not declared as a PRIMARY KEY as it is in the underlying
MyISAM tables. This is necessary because a MERGE table cannot
enforce uniqueness over the set of underlying tables.
After creating the MERGE table, you can do things like this:
mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
Note that you can also manipulate the `.MRG' file directly from
outside of the MySQL server:
shell> cd /mysql-data-directory/current-database
shell> ls -1 t1 t2 > total.MRG
shell> mysqladmin flush-tables
To remap a MERGE table to a different collection of MyISAM
tables, you can do one of the following:
-
DROP the table and re-create it.
-
Use
ALTER TABLE tbl_name UNION=(...) to change the list of underlying
tables.
-
Change the `.MRG' file and issue a
FLUSH TABLE statement for the
MERGE table and all underlying tables to force the storage engine to
read the new definition file.
MERGE tables can help you solve the following problems:
-
Easily manage a set of log tables. For example, you can put data from
different months into separate tables, compress some of them with
myisampack, and then create a MERGE table to use them as one.
-
Obtain more speed. You can split a big read-only table based on some
criteria, and then put individual tables on different disks.
A
MERGE table on this could be much faster than using
the big table. (You can also use a RAID table to get the same
kind of benefits.)
-
Do more efficient searches. If you know exactly what you are looking
for, you can search in just one of the split tables for some queries
and use a
MERGE table for others. You can even have many
different MERGE tables that use overlapping sets of tables.
-
Do more efficient repairs. It's easier to repair the individual tables that
are mapped to a
MERGE table than to repair a single really big table.
-
Instantly map many tables as one. A
MERGE table need not maintain
an index of its own because it uses the indexes of the individual tables.
As a result, MERGE table collections are very fast to create
or remap. (Note that you must still specify the index definitions when
you create a MERGE table, even though no indexes are created.)
-
If you have a set of tables that you join as a big table on demand or
batch, you should instead create a
MERGE table on them on demand.
This is much faster and will save a lot of disk space.
-
Exceed the file size limit for the operating system. Each
MyISAM table
is bound by this limit, but a collection of MyISAM tables is not.
-
You can create an alias or synonym for a
MyISAM table by defining a
MERGE table that maps to that single table. There should be no
really notable performance impact of doing this (only a couple of indirect
calls and memcpy() calls for each read).
The disadvantages of MERGE tables are:
-
You can use only identical
MyISAM tables for a MERGE table.
-
MERGE tables use more file descriptors. If 10 clients are using a
MERGE table that maps to 10 tables, the server uses
(10*10) + 10 file descriptors. (10 data file descriptors for each of the 10
clients, and 10 index file descriptors shared among the clients.)
-
Key reads are slower. When you read a key, the
MERGE
storage engine needs to issue a read on all underlying tables to check
which one most closely matches the given key. If you then do a ``read-next,''
the MERGE storage engine needs to search the read buffers
to find the next key. Only when one key buffer is used up, the storage engine
will need to read the next key block. This makes MERGE keys much slower
on eq_ref searches, but not much slower on ref searches.
See section 7.2.1 EXPLAIN Syntax (Get Information About a SELECT) for more information about eq_ref
and ref.
The following are the known problems with MERGE tables:
-
If you use
ALTER TABLE to change a MERGE table to another
table type, the mapping to the underlying tables is lost. Instead, the rows
from the underlying MyISAM tables are copied into the altered table,
which then is assigned the new type.
-
Before MySQL 4.1.1, all underlying tables and the
MERGE table itself
had to be in the same database.
-
REPLACE doesn't work.
-
You can't use
DROP TABLE,
ALTER TABLE,
DELETE FROM without a WHERE clause,
REPAIR TABLE,
TRUNCATE TABLE,
OPTIMIZE TABLE, or
ANALYZE TABLE
on any of the tables that are
mapped into a MERGE table that is ``open.'' If you do this, the
MERGE table may still refer to the original table and you will
get unexpected results. The easiest way to work around this deficiency
is to issue a FLUSH TABLES statement to ensure that no MERGE
tables remain ``open.''
-
A
MERGE table cannot maintain UNIQUE constraints over the
whole table. When you perform an INSERT, the data goes into the
first or last MyISAM table (depending on the value of the
INSERT_METHOD option). MySQL ensures that unique key values remain
unique within that MyISAM table, but not across all the tables in the
collection.
-
Before MySQL 3.23.49,
DELETE FROM merge_table used without a
WHERE clause only clears the mapping for the table. That is, it
incorrectly empties the `.MRG' file rather than deleting records from
the mapped tables.
-
Using
RENAME TABLE on an active MERGE table may corrupt the
table. This will be fixed in MySQL 4.1.x.
-
When you create a
MERGE table, there is no check whether the
underlying tables exist and have identical structure. When the MERGE
table is used, MySQL does a quick check that the record length for all
mapped tables is equal, but this is not foolproof. If you create a
MERGE table from dissimilar MyISAM tables, you are very likely
to run into strange problems.
-
Index order in the
MERGE table and its underlying tables should be
the same. If you use ALTER TABLE to add a UNIQUE index
to a table used in a MERGE table, and then use ALTER TABLE to
add a non-unique index on the MERGE table, the index order will be
different for the tables if there was an old non-unique index in the
underlying table. (This is because ALTER TABLE puts UNIQUE
indexes before non-unique indexes to be able to detect duplicate keys as early
as possible.) Consequently, queries may return unexpected results.
-
DROP TABLE on a table that is in use by a MERGE table does
not work on Windows because the MERGE storage engine does the table
mapping hidden from the upper layer of MySQL. Because Windows doesn't allow
you to delete files that are open, you first must flush all MERGE
tables (with FLUSH TABLES) or drop the MERGE table before
dropping the table.
The MEMORY storage engine creates tables with contents that are stored
in memory. Before MySQL 4.1, MEMORY tables are called HEAP
tables. As of 4.1, HEAP is a synonym for MEMORY, and
MEMORY is the preferred term.
Each MEMORY table is associated with one disk file. The filename
begins with the table name and has an extension of `.frm' to indicate
that it stores the table definition.
To specify explicitly that you want a MEMORY table, indicate that with
an ENGINE or TYPE table option:
CREATE TABLE t (i INT) ENGINE = MEMORY;
CREATE TABLE t (i INT) TYPE = HEAP;
MEMORY tables are stored in memory and use hash indexes by default.
This makes them very fast, and very useful for creating temporary tables.
However, when the server shuts down, all data stored in MEMORY
tables is lost. The tables continue to exist because their definitions
are stored in the `.frm' files on disk, but their contents will be
empty when the server restarts.
Here is an example that shows how you might create, use, and remove a
MEMORY table:
mysql> CREATE TABLE test TYPE=MEMORY
-> SELECT ip,SUM(downloads) AS down
-> FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;
MEMORY tables have the following characteristics:
-
Space for
MEMORY tables is allocated in small blocks. The tables use
100% dynamic hashing (on inserting). No overflow areas and no extra key
space are needed. There is no extra space needed for free lists. Deleted
rows are put in a linked list and are reused when you insert new data into
the table. MEMORY tables also don't have problems with deletes plus
inserts, which is common with hashed tables.
-
MEMORY tables allow up to 32 indexes per table, 16 columns per index,
and a maximum key length of 500 bytes.
-
Before MySQL 4.1, the
MEMORY storage engine implements only hash
indexes. From MySQL 4.1 on,
hash indexes are still the default, but
you can specify explicitly that a MEMORY
table index should be HASH or BTREE by adding a USING
clause:
CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
General characteristics of B-tree and hash indexes are described in
section 7.4.5 How MySQL Uses Indexes.
-
You can have non-unique keys in a
MEMORY table.
(This is an uncommon feature for implementations of hash indexes.)
-
If you have a hash index on a
MEMORY table that has a high degree of
key duplication (many index entries containing the same value), updates to
the table that affect key values and all deletes will be significantly
slower. The degree of slowdown is proportional to the degree of duplication
(or, inversely proportional to the index cardinality). You can use a
BTREE index to avoid this problem.
-
MEMORY tables use a fixed record length format.
-
MEMORY doesn't support BLOB or TEXT columns.
-
MEMORY doesn't support AUTO_INCREMENT columns before MySQL
4.1.0.
-
Prior to MySQL 4.0.2,
MEMORY doesn't support indexes on columns that
can contain NULL values.
-
MEMORY tables are shared between all clients (just like any other
non-TEMPORARY
table).
-
MEMORY table contents are stored in memory, which is a property that
MEMORY tables share with internal tables that the server creates on
the fly while processing queries. However, the two types of tables differ in
that MEMORY tables are not subject to storage conversion, whereas
internal tables are:
-
If an internal table becomes too large, the server automatically converts it
to an on-disk table. The size limit is determined by the value of the
tmp_table_size system variable.
-
MEMORY tables are never converted to disk tables. To ensure that you
don't accidentally do anything foolish, you can set the
max_heap_table_size system variable to impose a maximum size on
MEMORY tables. For individual tables, you can also specify a
MAX_ROWS table option in the CREATE TABLE statement.
-
The server needs enough extra memory to maintain all
MEMORY tables
that are in use at the same time.
-
To free memory used by a
MEMORY table if you no longer require its
contents, you should execute DELETE or
TRUNCATE TABLE, or else remove the table with DROP TABLE.
-
If you want to populate a
MEMORY table when the MySQL server starts,
you can use the --init-file option. For example, you can put
statements such as INSERT INTO ... SELECT or LOAD DATA INFILE
into the file to load the table from some persistent data source.
See section 5.2.1 mysqld Command-Line Options.
-
If you are using replication, the master server's
MEMORY tables
become empty when it is shut down and restarted. However, a slave is not
aware that these tables have become empty, so it will return out-of-date
content if you select data from them. Beginning with MySQL 4.0.18, when a
MEMORY table is used on the master for the first time since the master's
startup, a DELETE FROM statement is written to the master's binary
log automatically, thus synchronizing the slave to the master again. Note
that even with this strategy, the slave still has out-of-date data in the
table during the interval between the master's restart and its first use of
the table. But if you use the --init-file option to populate the
MEMORY table on the master at startup, it ensures that the failing
time interval is zero.
-
The memory needed for one row in a
MEMORY table is calculated using the
following expression:
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))
ALIGN() represents a round-up factor to cause the row length to be an
exact multiple of the char pointer size.
sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.
Sleepycat Software has provided MySQL with the Berkeley DB transactional
storage engine. This storage engine typically is called BDB for short.
Support for the BDB storage engine is included in MySQL source
distributions starting from version 3.23.34a and is activated in MySQL-Max
binary distributions.
BDB tables may have a greater chance of surviving crashes and are also
capable of COMMIT and ROLLBACK operations on transactions.
The MySQL source distribution comes with a BDB distribution that has a
couple of small patches to make it work more smoothly with MySQL.
You can't use a non-patched BDB version with MySQL.
We at MySQL AB are working in close cooperation with Sleepycat to keep the
quality of the MySQL/BDB interface high. (Even though Berkeley DB is in
itself very tested and reliable, the MySQL interface is still considered
gamma quality. We are improving and optimizing it.)
When it comes to support for any problems involving BDB tables, we
are committed to helping our users locate the problem and create a
reproducible test case. Any such test case will be forwarded to Sleepycat,
which in turn will help us find and fix the problem. As this is a two-stage
operation, any problems with BDB tables may take a little longer for
us to fix than for other storage engines. However, we anticipate no
significant difficulties with this procedure because the Berkeley DB code
itself is used in many applications other than MySQL.
For general information about Berkeley DB, please visit the Sleepycat Web site,
http://www.sleepycat.com/.
Currently, we know that the BDB storage engine works with the following
operating systems:
-
Linux 2.x Intel
-
Sun Solaris (SPARC and x86)
-
FreeBSD 4.x/5.x (x86, sparc64)
-
IBM AIX 4.3.x
-
SCO OpenServer
-
SCO UnixWare 7.1.x
BDB does not work with the following operating systems:
-
Linux 2.x Alpha
-
Linux 2.x AMD64
-
Linux 2.x IA-64
-
Linux 2.x s390
-
Mac OS X
Note: The preceding lists are not complete. We will update them as we
receive more information.
If you build MySQL from source with support for BDB tables, but the
following error occurs when you start mysqld, it means BDB
is not supported for your architecture:
bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init databases
In this case, you must rebuild MySQL without BDB table support or
start the server with the --skip-bdb option.
If you have downloaded a binary version of MySQL that includes support for
Berkeley DB, simply follow the usual binary distribution installation
instructions. (MySQL-Max distributions include BDB support.)
If you build MySQL from source, you can enable BDB support by running
configure with the --with-berkeley-db option in addition
to any other options that you normally use. Download a distribution for
MySQL 3.23.34 or newer, change location into its top-level directory,
and run this command:
shell> ./configure --with-berkeley-db [other-options]
For more information, see
section 2.7 Installing MySQL on Other Unix-Like Systems,
section 5.1.2 The mysqld-max Extended MySQL Server, and
See section 2.8 MySQL Installation Using a Source Distribution.
The following options to mysqld can be used to change the behavior of
the BDB storage engine:
--bdb-home=path
-
The base directory for
BDB tables. This should be the same directory
you
use for --datadir.
--bdb-lock-detect=method
-
The
BDB lock detection method. The option value should be
DEFAULT, OLDEST, RANDOM, or YOUNGEST.
--bdb-logdir=path
-
The
BDB log file directory.
--bdb-no-recover
-
Don't start Berkeley DB in recover mode.
--bdb-no-sync
-
Don't synchronously flush the
BDB logs.
--bdb-shared-data
-
Start Berkeley DB in multi-process mode. (Don't use
DB_PRIVATE when
initializing Berkeley DB.)
--bdb-tmpdir=path
-
The
BDB temporary file directory.
--skip-bdb
-
Disable the
BDB storage engine.
See section 5.2.1 mysqld Command-Line Options.
The following system variable affects the behavior of
BDB tables:
bdb_max_lock
-
The maximum number of locks you can have active on a
BDB table.
See section 5.2.3 Server System Variables.
If you use the --skip-bdb option, MySQL will not initialize the Berkeley DB
library and this will save a lot of memory. However, if you use this
option, you cannot use BDB tables. If you try to create a BDB
table, MySQL will create a MyISAM table instead.
Normally, you should start mysqld without the --bdb-no-recover
option if you intend to use BDB tables. However, this may give you
problems when you try to start mysqld if the BDB log files are
corrupted.
See section 2.9.2.3 Starting and Troubleshooting the MySQL Server.
With the bdb_max_lock variable, you can specify the maximum number of
locks that can be active on a BDB table. The default is
10,000. You should increase this if errors such as the following occur
when you perform long transactions or when mysqld has to examine
many rows to execute a query:
bdb: Lock table is out of available locks
Got error 12 from ...
You may also want to change the binlog_cache_size and
max_binlog_cache_size variables if you are using large
multiple-statement transactions.
See section 5.9.4 The Binary Log.
Each BDB table is stored on disk in two files. The files have
names that begin with the table name and have an extension to indicate the
file type. An `.frm' file stores the table definition, and a `.db'
file contains the table data and indexes.
To specify explicitly that you want a BDB table, indicate that with
an ENGINE or TYPE table option:
CREATE TABLE t (i INT) ENGINE = BDB;
CREATE TABLE t (i INT) TYPE = BDB;
BerkeleyDB is a synonym for BDB in the ENGINE or
TYPE option.
The BDB storage engine provides transactional tables. The way you use
these tables depends on the autocommit mode:
-
If you are running with autocommit enabled (which is the default),
changes to
BDB tables are committed immediately and cannot be rolled
back.
-
If you are running with autocommit disabled, changes do not become
permanent until you execute a
COMMIT statement. Instead of
committing, you can execute ROLLBACK to forget the changes.
You can start a transaction with
the BEGIN WORK statement to suspend autocommit, or with
SET AUTOCOMMIT=0 to disable autocommit explicitly.
See section 13.4.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax.
The BDB storage engine has the following characteristics:
-
BDB tables can have up to 31 indexes per table, 16 columns per index,
and a maximum key size of 1024 bytes (500 bytes before MySQL 4.0).
-
MySQL requires a
PRIMARY KEY in each BDB table so that each row
can be uniquely identified. If you don't create one explicitly,
MySQL creates and maintains a hidden PRIMARY KEY for
you. The hidden key has a length of five bytes and is incremented for each
insert attempt.
-
The
PRIMARY KEY will be faster than any other index, because the
PRIMARY KEY is stored together with the row data. The other indexes
are stored as the key data + the PRIMARY KEY, so it's important to
keep the PRIMARY KEY as short as possible to save disk space and get
better speed.
This behavior is similar to that of InnoDB, where shorter primary
keys save space not only in the primary index but in secondary indexes as
well.
-
If all columns you access in a
BDB table are part of the same index or
part of the primary key, MySQL can execute the query
without having to access the actual row. In a MyISAM table, this can
be done only if the columns are part of the same index.
-
Sequential scanning is slower than for
MyISAM tables because the data
in BDB tables is stored in B-trees and not in a separate data file.
-
Key values are not prefix- or suffix-compressed like key values in
MyISAM tables. In other words, key information takes a little more
space in BDB tables compared to MyISAM tables.
-
There are often holes in the
BDB table to allow you to insert
new rows in the middle of the index tree. This makes BDB tables
somewhat larger than MyISAM tables.
-
SELECT COUNT(*) FROM tbl_name is slow for BDB tables, because
no row count is maintained in the table.
-
The optimizer needs to know the approximate number of rows in the table.
MySQL solves this by counting inserts and maintaining this in a separate
segment in each
BDB table. If you don't issue a lot of DELETE
or ROLLBACK statements, this number should be accurate enough for
the MySQL optimizer. However, MySQL stores the number only on close, so
it may be incorrect if the server terminates unexpectedly. It should not
be fatal even if this number is not 100% correct. You can update the row
count by using ANALYZE TABLE or OPTIMIZE TABLE.
See section 13.5.2.1 ANALYZE TABLE Syntax and
section 13.5.2.5 OPTIMIZE TABLE Syntax.
-
Internal locking in
BDB tables is done at the page level.
-
LOCK TABLES works on BDB tables as with other tables. If you
don't use LOCK TABLE, MySQL issues an internal multiple-write lock on
the table (a lock that doesn't block other writers) to ensure that the
table will be properly locked if another thread issues a table lock.
-
To be able to roll back transactions, the
BDB storage engine
maintains log files. For maximum performance, you can use the
--bdb-logdir option to place the BDB logs on a different disk
than the one where your databases are located.
-
MySQL performs a checkpoint each time a new
BDB log file is started,
and removes any BDB log files that are not needed for current
transactions. You can also use FLUSH LOGS at any time to checkpoint
the Berkeley DB tables.
For disaster recovery, you should use table backups plus MySQL's binary log.
See section 5.7.1 Database Backups.
Warning: If you delete old log files that are still in use,
BDB will not be able to do recovery at all and you may lose data if
something goes wrong.
-
Applications must always be prepared to handle cases where
any change of a
BDB table may cause an automatic rollback and any
read may fail with a deadlock error.
-
If you get full disk with a
BDB table, you will get an error
(probably error 28) and the transaction should roll back. This contrasts
with MyISAM and ISAM tables, for which mysqld will wait
for enough free disk before continuing.
-
It's very slow to open many
BDB tables at the same time. If you are
going to use BDB tables, you should not have a very large table cache
(for example, with a size larger than 256) and you should use the
--no-auto-rehash option when you use the mysql client. We
plan to partly fix this in 4.0.
-
SHOW TABLE STATUS doesn't yet provide very much information for
BDB
tables.
-
Optimize performance.
-
Change to not use page locks at all for table scanning operations.
The following list indicates restrictions that you must observe when using
BDB tables:
-
Each
BDB table stores in the `.db' file the path to the file as it
was created. This was done to be able to detect locks in a multi-user
environment that supports symlinks. However, the consequence is that
BDB table files cannot be moved from one database directory to
another.
-
When making backups of
BDB tables, you must either use
mysqldump or else make a backup that includes the files for each
BDB table (the `.frm' and `.db' files) as well as the
BDB log files. The BDB storage engine stores unfinished
transactions in its log files and requires them to be present when
mysqld starts. The BDB logs are the files in the data
directory with names of the form `log.XXXXXXXXXX' (ten digits).
-
If a column that allows
NULL values has a unique index, only a single
NULL value is allowed. This differs from other storage engines.
The EXAMPLE storage engine was added in MySQL 4.1.3. It is a
``stub'' engine that does nothing. Its purpose is to serve as an example in
the MySQL source code that illustrates how to begin writing new storage
engines. As such, it is primarily of interest to developers.
To examine the source for the EXAMPLE engine, look in the
`sql/examples' directory of a source distribution for MySQL 4.1.3 or
newer.
To enable this storage engine, use the --with-example-storage-engine
option to configure when you build MySQL.
When you create an EXAMPLE table, the server creates a table
definition file in the database directory. The file begins with the table
name and has an `.frm' extension. No other files are created. No data
can be stored into the table or retrieved from it.
mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE;
Query OK, 0 rows affected (0.78 sec)
mysql> INSERT INTO test VALUES(1),(2),(3);
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option
mysql> SELECT * FROM test;
Empty set (0.31 sec)
The EXAMPLE storage engine does not support indexing.
The FEDERATED storage engine was added in MySQL 5.0.3. It is a
storage engine that accesses data in tables of remote databases rather than in
local tables.
To examine the source for the FEDERATED engine, look in the
`sql' directory of a source distribution for MySQL 5.0.3 or
newer.
To enable this storage engine, use the --with-federated-storage-engine
option to configure when you build MySQL.
When you create a FEDERATED table, the server creates a table
definition file in the database directory. The file begins with the table
name and has an `.frm' extension. No other files are created, because
the actual data is in a remote database. This differs from the way that
storage engines for local tables work.
For local database tables, data files are local. For example, if you create
a MyISAM table named users, the MyISAM handler creates a
data file named users.MYD. A handler for local tables reads, inserts,
deletes, and updates data in local data files, and records are stored in a
format particular to the handler. To read records, the handler must parse
data into columns. To write records, column values must be converted to
the row format used by the handler and written to the local data file.
With the MySQL FEDERATED storage engine, there are no local data
files for a table (for example, there is no `.MYD' file). Instead, a
remote database stores the data that normally would be in the table. This
necessitates the use of the MySQL client API to read, delete, update,
and insert data. Data retrieval is initiated via a SELECT * FROM
tbl_name SQL statement. To read the result, rows are fetched one
at a time by using the mysql_fetch_row() C API function, and then
converted from the columns in the SELECT result set to the format
that the FEDERATED handler expects.
The basic flow is as follows:
-
SQL calls issues locally
-
MySQL handler API (data in handler format)
-
MySQL client API (data converted to SQL calls)
-
Remote database -> MySQL client API
-
Convert result sets (if any) to handler format
-
Handler API -> Result rows or rows-affected count to local
The procedure for using FEDERATED tables is very simple. Normally, you
have two servers running, either both on the same host or on different hosts.
(It is also possible for a FEDERATED table to use another table that
is managed by the same server, though there is little point in doing so.)
First, you must have a table on the remote server that you want to access with
the FEDERATED table. Suppose that the remote table is in the
federated database and is defined like this:
CREATE TABLE test_table (
id int(20) NOT NULL auto_increment,
name varchar(32) NOT NULL default '',
other int(20) NOT NULL default '0',
PRIMARY KEY (id),
KEY name (name),
KEY other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1 ;
The ENGINE table option could name any storage engine; the table
need not be a MyISAM table.
Next, create a FEDERATED table for accessing the remote table.
The server where you will create the FEDERATED table is the ``client
server.'' On this server, create the table as follows:
CREATE TABLE federated_table (
id int(20) NOT NULL auto_increment,
name varchar(32) NOT NULL default '',
other int(20) NOT NULL default '0',
PRIMARY KEY (id),
KEY name (name),
KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_host:9306/federated/test_table';
The structure of this table must be exactly the same as the remote table,
except that the ENGINE table option should be FEDERATED and
the COMMENT table option is a connection string that indicates to
the FEDERATED engine how to connect to the remote server.
The FEDERATED engine will create only the `test_table.frm' file in
the federated database.
The remote host information indicates the remote server to which your
``client'' server will connect, and the database and table information
indicates which remote table to use as the ``data file.'' In the example,
the remote server is indicated to be running as remote_host on port
9306, so you want to start that server so that it is indeed listening to
port 9306.
The general form of the connection string in the COMMENT option is as
follows:
scheme://user_name[:password]@host_name[:port_num]:/db_name/tbl_name
Only mysql is supported as the scheme at this point, and the
password and port number are optional.
Here are some example connection strings:
COMMENT='mysql://username:password@hostname:port/database/tablename'
COMMENT='mysql://username@hostname/database/tablename'
COMMENT='mysql://username:password@hostname/database/tablename'
The use of COMMENT for specifying the connection string is non-optimal
and likely will change in MySQL 5.1. Keep this in mind when you use
FEDERATED tables, because it means you'll need to make some
modifications when that happens.
Also, because a password is stored in the connection string as plain text,
it can be seen by any user who can use SHOW CREATE TABLE or SHOW TABLE STATUS for the FEDERATED table.
What the FEDERATED storage engine does and doesn't support:
-
In the first version, the remote server must be a MySQL server. Support by
FEDERATED for other database engines may be be added in the future.
-
The remote table that a
FEDERATED table points to must exist
before you try to access the table through the FEDERATED table.
-
It is possible for one
FEDERATED table to point to another, but
you must be careful not to create a loop. You know and have heard the
screeching of audio feedback? You know what you see visually when you
place two mirrors in front of each other, how the reflection continues
for eternity? Well, need we say more?!
-
There is no support for transactions.
-
There is no way for the
FEDERATED engine to know if the remote table
has changed. The reason for this is that this table has to work like a
data file that would never be written to by anything other than the
database. The integrity of the data in the local table could be breached
if there was any change to the remote database.
-
The
FEDERATED storage engine supports SELECT, INSERT,
UPDATE, DELETE, and indexes. It does not support ALTER
TABLE, DROP TABLE, or any other Data Definition Language statements.
The first implementation does not use Prepared statements. It remains
to be seen whether the limited subset of the client API for the server
supports this capability.
-
The implementation uses
SELECT, INSERT, UPDATE,
DELETE and not HANDLER.
-
FEDERATED tables do not work with the query cache.
Some of these limitations may be lifted in future versions of the
FEDERATED handler.
The ARCHIVE storage engine was added in MySQL 4.1.3.
It is used for storing large amounts of data without indexes in a very
small footprint.
To enable this storage engine, use the --with-archive-storage-engine
option to configure when you build MySQL.
When you create an ARCHIVE table, the server creates a table definition
file in the database directory. The file begins with the table name and has
an `.frm' extension. The storage engine creates other files, all having
names beginning with the table name. The data and metadata files have
extensions of `.ARZ' and `.ARM'. An `.ARN' file may appear
during optimization operations.
The ARCHIVE engine supports only INSERT and SELECT. (No
deletes, replaces, or updates.) A SELECT performs a complete table
scan. Records are compressed as they are inserted. Use of OPTIMIZE
TABLE can analyze the table and pack it into a smaller format.
The ARCHIVE engine uses row-level locking.
The CSV storage engine was added in MySQL 4.1.4. This engine stores
data in text files using comma-separated-values format.
To enable this storage engine, use the --with-csv-storage-engine
option to configure when you build MySQL.
When you create a CSV table, the server creates a table definition
file in the database directory. The file begins with the table name and has
an `.frm' extension. The storage engine also creates a data file. Its
name begins with the table name and has a `.CSV' extension. The data
file is a plain text file. When you store data into the table, the storage
engine saves it into the data file in CSV format.
mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;
Query OK, 0 rows affected (0.12 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;
+------+------------+
| i | c |
+------+------------+
| 1 | record one |
| 2 | record two |
+------+------------+
2 rows in set (0.00 sec)
If you examine the `test.CSV' file in the database directory after
executing the preceding statements, its contents look like this:
"1","record one"
"2","record two"
The CSV storage engine does not support indexing.
The original storage engine in MySQL was the ISAM engine. It was the
only storage engine available until MySQL 3.23, when the improved
MyISAM engine was introduced as the default. ISAM now is
deprecated. As of MySQL 4.1, it's included in the source but not enabled in
binary distributions. It will disappear in MySQL 5.0.
Embedded MySQL server versions do not support ISAM tables by default.
Due to the deprecated status of ISAM, and because MyISAM is
an improvement over ISAM, you are advised to convert any remaining
ISAM tables to MySAM as soon as possible. To convert an
ISAM table to a MyISAM table, use an ALTER TABLE
statement:
mysql> ALTER TABLE tbl_name TYPE = MYISAM;
For more information about MyISAM, see
section 14.1 The MyISAM Storage Engine.
Each ISAM table is stored on disk in three files. The files have
names that begin with the table name and have an extension to indicate the
file type. An `.frm' file stores the table definition. The data file
has an `.ISD' extension. The index file has an `.ISM'
extension.
ISAM uses B-tree indexes.
You can check or repair ISAM tables with the isamchk utility.
See section 5.7.2.7 Using myisamchk for Crash Recovery.
ISAM has the following properties:
- Compressed and fixed-length keys
- Fixed and dynamic record length
- 16 indexes per table, with 16 key parts per key
- Maximum key length 256 bytes (default)
- Data values are stored in machine format; this is fast, but machine/OS dependent
Many of the properties of MyISAM tables are also true for ISAM
tables. However, there are also many differences. The following list
describes some of the ways that ISAM is distinct from MyISAM:
-
Not binary portable across OS/platforms.
-
Can't handle tables larger than 4GB.
-
Only supports prefix compression on strings.
-
Smaller (more restrictive) key limits.
-
Dynamic tables become more fragmented.
-
Doesn't support
MERGE tables.
-
Tables are checked and repaired with
isamchk rather than with
myisamchk.
-
Tables are compressed with
pack_isam rather than with myisampack.
-
Cannot be used with the
BACKUP TABLE or RESTORE TABLE
backup-related statements.
-
Cannot be used with the
CHECK TABLE, REPAIR TABLE, OPTIMIZE
TABLE, or ANALYZE TABLE table-maintenance statements.
-
No support for full-text searching or spatial data types.
-
No support for multiple character sets per table.
-
Indexes cannot be assigned to specific key caches.
Go to the first, previous, next, last section, table of contents.
|
