14.6.5.7 Limits on InnoDB Tables

Warning

Do not convert MySQL system tables in the
mysql database from MyISAM
to InnoDB tables! This is an unsupported
operation. If you do this, MySQL does not restart until you
restore the old system tables from a backup or re-generate them
with the mysql_install_db program.

Warning

It is not a good idea to configure InnoDB to
use data files or log files on NFS volumes. Otherwise, the files
might be locked by other processes and become unavailable for
use by MySQL.

The InnoDB internal maximum key length is
3500 bytes, but MySQL itself restricts this to 3072 bytes.
This limit applies to the length of the combined index key in
a multi-column index.

The maximum row length, except for variable-length columns
(VARBINARY,
VARCHAR,
BLOB and
TEXT), is slightly less than
half of a database page. That is, the maximum row length is
about 8000 bytes. LONGBLOB and
LONGTEXT
columns must be less than 4GB, and the total row length,
including BLOB and
TEXT columns, must be less than
4GB.

If a row is less than half a page long, all of it is stored
locally within the page. If it exceeds half a page,
variable-length columns are chosen for external off-page
storage until the row fits within half a page, as described in
Section 14.6.6.2, “File Space Management”.

The row size for BLOB columns
that are chosen for external off-page storage should not
exceed 10% of the combined redo
log file size. If the row size exceeds 10% of the
combined redo log file size, InnoDB could
overwrite the most recent checkpoint which may result in lost
data during crash recovery. (Bug#69477).

Although InnoDB supports row sizes larger
than 65,535 bytes internally, MySQL itself imposes a row-size
limit of 65,535 for the combined size of all columns:

On some older operating systems, files must be less than 2GB.
This is not a limitation of InnoDB itself,
but if you require a large tablespace, you will need to
configure it using several smaller data files rather than one
or a file large data files.

The combined size of the InnoDB log files
must be less than 4GB.

The minimum tablespace size is 10MB. The maximum tablespace
size is four billion database pages (64TB). This is also the
maximum size for a table.

The default database page size in InnoDB is
16KB.

Note

Changing the page size is not a supported operation and
there is no guarantee that
InnoDB will function normally
with a page size other than 16KB. Problems compiling or
running InnoDB may occur. In particular,
ROW_FORMAT=COMPRESSED in the
InnoDB Plugin assumes that the page size
is at most 16KB and uses 14-bit pointers.

A version of InnoDB built for
one page size cannot use data files or log files from a
version built for a different page size. This limitation
could affect restore or downgrade operations using data from
MySQL 5.6, which does support page sizes other than 16KB.

Index Types

InnoDB tables do not support
FULLTEXT indexes.

InnoDB tables support spatial data types,
but not indexes on them.

Restrictions on InnoDB Tables

ANALYZE TABLE determines index
cardinality (as displayed in the
Cardinality column of
SHOW INDEX output) by doing
eight random dives to each of the index trees and updating
index cardinality estimates accordingly. Because these are
only estimates, repeated runs of ANALYZE
TABLE may produce different numbers. This makes
ANALYZE TABLE fast on
InnoDB tables but not 100% accurate because
it does not take all rows into account.

If statements or transactions are running on a table and
ANALYZE TABLE is run on the
same table followed by a second ANALYZE
TABLE operation, the second
ANALYZE TABLE operation is
blocked until the statements or transactions are completed.
This behaviour occurs because ANALYZE
TABLE marks the currently loaded table definition as
obsolete when ANALYZE TABLE is
finished running. New statements or transactions (including a
second ANALYZE TABLE statement)
must load the new table definition into the table cache, which
cannot occur until currently running statements or
transactions are completed and the old table definition is
purged. Loading multiple concurrent table definitions is not
supported.

SHOW TABLE STATUS does not give
accurate statistics on
InnoDB tables, except for the physical size
reserved by the table. The row count is only a rough estimate
used in SQL optimization.

InnoDB does not keep an internal count of
rows in a table because concurrent transactions might
“see” different numbers of rows at the same time.
To process a SELECT COUNT(*) FROM t
statement, InnoDB scans an index of the
table, which takes some time if the index is not entirely in
the buffer pool. If your table does not change often, using
the MySQL query cache is a good solution. To get a fast count,
you have to use a counter table you create yourself and let
your application update it according to the inserts and
deletes it does. If an approximate row count is sufficient,
SHOW TABLE STATUS can be used.
See Section 14.6.8, “InnoDB Performance Tuning Tips”.

On Windows, InnoDB always stores database
and table names internally in lowercase. To move databases in
a binary format from Unix to Windows or from Windows to Unix,
create all databases and tables using lowercase names.

An AUTO_INCREMENT column
ai_col must be defined as part of
an index such that it is possible to perform the equivalent of
an indexed SELECT
MAX(ai_col) lookup on the
table to obtain the maximum column value. Typically, this is
achieved by making the column the first column of some table
index.

InnoDB sets an exclusive lock on the end of
the index associated with the
AUTO_INCREMENT column while initializing a
previously specified AUTO_INCREMENT column
on a table.

When you restart the MySQL server, InnoDB
may reuse an old value that was generated for an
AUTO_INCREMENT column but never stored
(that is, a value that was generated during an old transaction
that was rolled back).

When an AUTO_INCREMENT integer column runs
out of values, a subsequent INSERT
operation returns a duplicate-key error. This is general MySQL
behavior, similar to how MyISAM works.

DELETE FROM
tbl_name does not
regenerate the table but instead deletes all rows, one by one.

The LOAD TABLE FROM MASTER
statement for setting up replication slave servers does not
work for InnoDB tables. A workaround is to
alter the table to MyISAM on the master,
then do the load, and after that alter the master table back
to InnoDB. Do not do this if the tables use
InnoDB-specific features such as foreign
keys.

Currently, cascaded foreign key actions do not activate
triggers.

You cannot create a table with a column name that matches the
name of an internal InnoDB column (including
DB_ROW_ID, DB_TRX_ID,
DB_ROLL_PTR, and
DB_MIX_ID). In versions of MySQL before
5.1.10 this would cause a crash, since 5.1.10 the server will
report error 1005 and refers to error –1 in the error
message. This restriction applies only to use of the names in
uppercase.

Locking and Transactions

LOCK TABLES acquires two locks
on each table if innodb_table_locks=1 (the
default). In addition to a table lock on the MySQL layer, it
also acquires an InnoDB table lock.
Versions of MySQL before 4.1.2 did not acquire
InnoDB table locks; the old behavior can be
selected by setting innodb_table_locks=0.
If no InnoDB table lock is acquired,
LOCK TABLES completes even if
some records of the tables are being locked by other
transactions.

All InnoDB locks held by a transaction are
released when the transaction is committed or aborted. Thus,
it does not make much sense to invoke
LOCK TABLES on
InnoDB tables in
autocommit=1 mode because the
acquired InnoDB table locks would be
released immediately.

InnoDB has a limit of 1023 concurrent
transactions that have created undo records by modifying data.
Workarounds include keeping transactions as small and fast as
possible, delaying changes until near the end of the
transaction, and using stored routines to reduce client/server
latency delays. Applications should commit transactions before
doing time-consuming client-side operations.