Maximums and Minimums

The index key prefix length limit is 3072 bytes for
InnoDB tables that use
DYNAMIC
or
COMPRESSED
row format.

The index key prefix length limit is 767 bytes for
InnoDB tables that use
REDUNDANT
or
COMPACT
row format. For example, you might hit this limit with a
column prefix
index of more than 191 characters on a
TEXT or VARCHAR
column, assuming a utf8mb4 character set
and the maximum of 4 bytes for each character.

Attempting to use an index key prefix length that exceeds
the limit returns an error.

The limits that apply to index key prefixes also apply to
full-column index keys.

If you reduce the InnoDBpage size to 8KB or
4KB by specifying the
innodb_page_size option
when creating the MySQL instance, the maximum length of the
index key is lowered proportionally, based on the limit of
3072 bytes for a 16KB page size. That is, the maximum index
key length is 1536 bytes when the page size is 8KB, and 768
bytes when the page size is 4KB.

A maximum of 16 columns is permitted for multicolumn
indexes. Exceeding the limit returns an error.

The maximum row length, except for variable-length columns
(VARBINARY,
VARCHAR,
BLOB and
TEXT), is slightly less than
half of a page for 4KB, 8KB, 16KB, and 32KB page sizes. For
example, the maximum row length for the default
innodb_page_size of 16KB is
about 8000 bytes. For an InnoDB page size
of 64KB, the maximum row length is about 16000 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 15.11.2, “File Space Management”.

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, configure it
using several smaller data files rather than one large data
file.

The combined size of the InnoDB log files
can be up to 512GB.

The minimum tablespace size is slightly larger than 10MB.
The maximum tablespace size depends on the
InnoDB page size.

Table 15.3 InnoDB Maximum Tablespace Size

InnoDB Page Size

Maximum Tablespace Size

4KB

16TB

8KB

32TB

16KB

64TB

32KB

128TB

64KB

256TB

The maximum tablespace size is also the maximum size for a
table.

The path of a tablespace file, including the file name,
cannot exceed the MAX_PATH limit on
Windows. Prior to Windows 10, the
MAX_PATH limit is 260 characters. As of
Windows 10, version 1607, MAX_PATH
limitations are removed from common Win32 file and directory
functions, but you must enable the new behavior.

The default page size in InnoDB is 16KB.
You can increase or decrease the page size by configuring
the innodb_page_size option
when creating the MySQL instance.

32KB and 64KB page sizes are supported, but
ROW_FORMAT=COMPRESSED is unsupported for
page sizes greater than 16KB. For both 32KB and 64KB page
sizes, the maximum record size is 16KB. For
innodb_page_size=32KB,
extent size is 2MB. For
innodb_page_size=64KB,
extent size is 4MB.

A MySQL instance using a particular
InnoDB page size cannot use data files or
log files from an instance that uses a different page size.

Restrictions on InnoDB Tables

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

MySQL uses index cardinality estimates in join optimization.
If a join is not optimized in the right way, try using
ANALYZE TABLE. In the few
cases that ANALYZE TABLE does
not produce values good enough for your particular tables,
you can use FORCE INDEX with your queries
to force the use of a particular index, or set the
max_seeks_for_key system
variable to ensure that MySQL prefers index lookups over
table scans. See Section B.4.5, “Optimizer-Related Issues”.

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 behavior 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. Consequently, SELECT COUNT(*)
statements only count rows visible to the current
transaction.

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 an AUTO_INCREMENT integer column
runs out of values, a subsequent INSERT
operation returns a duplicate-key error. This is general
MySQL behavior.

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

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). This restriction applies to
use of the names in any letter case.

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.