You are here
Limitations of MySQL
Often asked but informations are spread around: The limitations of MySQL.
If you know any other MySQL limitations, please let us know.
Contents
- General limitations of MySQL
- Limitations of MySQL 4.1
- Limitations of Joins
- Limitations of the MyISAM storage engine
- Limitations of MySQL 5.0
- Limitations of Joins
- Limitations of the MyISAM storage engine
- Limitations of InnoDB
- Limitations of MySQL 5.1
- Limitations of Joins
- Limitations of Partitions
- Limitations of MySQL Cluster
- Limitations in Galera Cluster for MySQL
General limitations of MySQL
[Lit.]32-bit binaries cannot address more than 4 Gbyte of memory. This is not a MySQL limitation, this is a technical limitation.
BLOB
's are limited to 1 Gbyte in size even thought you use LONGBLOB
because of a limitation in the MySQL protocol: The protocol limit for max_allowed_packet
is 1GB.
Limitations of MySQL 4.1
Limitations of Joins
In MySQL 4.1, the maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. [Lit.]
Limitations of the MyISAM storage engine
There is a limitation of 232 (~4.2 Mia) rows in a MyISAM table. You can increase this limitation if you build MySQL with the --with-big-tables option then the row limitation is increased to 264 (1.8 * 1019) rows. [Lit.]
Limitations of MySQL 5.0
Limitations of Joins
The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. This also applies to LEFT
and RIGHT OUTER JOINS
. [Lit.]
Limitations of the MyISAM storage engine
Large files up to 63-bit file length are supported.
There is a limitation of 264 (1.8 * 1019) rows in a MyISAM table.
The maximum number of indexes per MyISAM table is 64. You can configure the build by invoking configure with the --with-max-indexes=N option, where N is the maximum number of indexes to permit per MyISAM table. N must be less than or equal to 128.
The maximum number of columns per index is 16.
The maximum key length is 1000 bytes. This can be changed by changing the source and recompiling. [Lit.]
Limitations of the InnoDB storage engine
A table cannot contain more than 1000 columns.
The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.
The maximum row length, except for VARCHAR
, BLOB
and TEXT
columns, 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 4 Gbyte, and the total row length, including also BLOB
and TEXT
columns, must be less than 4 Gbyte.
Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARCHAR
columns with a combined size larger than 65535.
The maximum tablespace size is 4 Mia database pages (64 Tbyte). This is also the maximum size for a table. [Lit.]
Limitations of MySQL 5.1
Limitations of Joins
The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. [Lit.]
Limitations of Partitions
The limitation of partitions with MySQL is 1024 (internal mail). But one have to increase open_files_limit
. See also: [Lit.]
Limitations of MySQL Cluster
Max attributes/columns in an index: 32
Max number of attributes (columns and indexes) in a table: 128
Max number of table: 1792 (v5.0)
Max size in bytes of a row is 8052 byte, excluding blobs which are stored separately.
Max number of nodes in a cluster: 63, max. number of data nodes: 48 (in v5.0/5.1)
Max number of nodes in a cluster: 255 in CGE.
Max number of metadata objects: 20320.
Max attribute name length: 31 characters.
Max database + table name length: 122 characters.
For maximum of configuration parameters see Chapter Data Node Configuration Parameters
[1] Limits Associated with Database Objects in MySQL Cluster
Limitations in Galera Cluster for MySQL
- Galera replication originally only worked with InnoDB storage engine, but it now also supports MyISAM storage engine. Any writes to other table types, including system (mysql.*) tables are not replicated. However, DDL statements are replicated in statement level, and changes to mysql.* tables will get replicated that way. So, you can safely issue:
CREATE USER ...
, but issuing:INSERT INTO mysql.user ...
, will not be replicated. - MyISAM replication is recent and should be considered experimental. Non-deterministic functions like
NOW()
are not supported. The Configurator for Galera enables wsrep_replicate_myisam by default. DELETE
operation is unsupported on tables without primary key. Also rows in tables without primary key may appear in different order on different nodes. As a resultSELECT ... LIMIT ...
may return slightly different sets.- Unsupported queries:
LOCK/UNLOCK TABLES
cannot be supported in multi-master setups.- Lock functions (
GET_LOCK(), RELEASE_LOCK(), ...
)
- Query log cannot be directed to table. If you enable query logging, you must forward the log to a file:
log_output = FILE
.
Use general_log and general_log_file to choose query logging and the log file name. - Maximum allowed transaction size is defined by
wsrep_max_ws_rows
andwsrep_max_ws_size
. Anything bigger (e.g. hugeLOAD DATA
) will be rejected. - Due to cluster level optimistic concurrency control, transaction issuing
COMMIT
may still be aborted at that stage. There can be two transactions writing to same rows and committing in separate cluster nodes, and only one of the them can successfully commit. The failing one will be aborted. For cluster level aborts, MySQL/Galera cluster gives back deadlock error:Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)
. - XA transactions can not be supported due to possible rollback on commit.
See also: Differences from a Standalone MySQL Server and MariaDB Galera Cluster - Known Limitations.