MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6 Limits on InnoDB Tables

Limits on InnoDB tables are described under the following topics in this section:


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 regenerate them by reinitializing the data directory (see Section 2.10.1, “Initializing the Data Directory”).


Before using NFS with InnoDB, review potential issues outlined in Using NFS with MySQL.

Maximums and Minimums
  • A table can contain a maximum of 1017 columns (raised in MySQL 5.6.9 from the earlier limit of 1000). Virtual generated columns are included in this limit.

  • A table can contain a maximum of 64 secondary indexes.

  • If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

    innodb_large_prefix is deprecated and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

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

    Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the master if it cannot also be enabled on slaves.

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

  • If you reduce the InnoDB page 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.

    ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed
  • 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. However, for an InnoDB page size of 64KB, the maximum row length is approximately 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 14.12.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:

    mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
        -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
        -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
    ERROR 1118 (42000): Row size too large. The maximum row size for the
    used table type, not counting BLOBs, is 65535. You have to change some
    columns to TEXT or BLOBs

    See Section 8.4.7, “Limits on Table Column Count and Row Size”.

  • 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 14.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.

  • Tablespace files cannot exceed 4GB on Windows 32-bit systems (Bug #80149).

  • 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.

    ROW_FORMAT=COMPRESSED in the Barracuda file format assumes that the page size is at most 16KB and uses 14-bit pointers.

    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=32k, extent size is 2MB. For innodb_page_size=64k, 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.

    You can make the statistics collected by ANALYZE TABLE more precise and more stable by turning on the innodb_stats_persistent configuration option, as explained in Section, “Configuring Persistent Optimizer Statistics Parameters”. When that setting is enabled, it is important to run ANALYZE TABLE after major changes to indexed column data, because the statistics are not recalculated periodically (such as after a server restart).

    If the persistent statistics setting is enabled, you can change the number of random dives by modifying the innodb_stats_persistent_sample_pages system variable. If the persistent statistics setting is disabled, modify the innodb_stats_transient_sample_pages system variable instead.

    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”.

  • 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.

    For information about how InnoDB processes SELECT COUNT(*) statements, refer to the COUNT() description in Section 12.20.1, “Aggregate (GROUP BY) Function Descriptions”.

  • 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.

    With innodb_autoinc_lock_mode=0, InnoDB uses a special AUTO-INC table lock mode where the lock is obtained and held to the end of the current SQL statement while accessing the auto-increment counter. Other clients cannot insert into the table while the AUTO-INC table lock is held. The same behavior occurs for bulk inserts with innodb_autoinc_lock_mode=1. Table-level AUTO-INC locks are not used with innodb_autoinc_lock_mode=2. For more information, See Section, “AUTO_INCREMENT Handling in InnoDB”.

  • 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.

  • 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.

    mysql> CREATE TABLE t1 (c1 INT, db_row_id INT) ENGINE=INNODB;
    ERROR 1166 (42000): Incorrect column name 'db_row_id'
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.

    In MySQL 5.7, innodb_table_locks=0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.

  • 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.

  • You cannot lock additional tables in the middle of a transaction because LOCK TABLES performs an implicit COMMIT and UNLOCK TABLES.

  • For limits associated with concurrent read-write transactions, see Section 14.6.7, “Undo Logs”.