MySQL – Maximum Values

What is the maximum size of a table?

The maximum size of a table is limited by the MySQL version, the operating system and in particular the file system. In most cases, this will be the size of your hard drive. Many “Linux” systems impose a limit of 2GB per file.

MySQL 3.23 (and above) can be compiled to allow limited tables only by the file system.

What is the maximum size of a query?

The total size of a query string is limited by the max_allowed_packet variable (use the show variables command to get its value).
The size of this variable is 1MB by default, but can be increased (up to 15MB for version 3.23).

Internally, MySQL can handle string constants in WHERE clauses as large as the size of TEXT and BLOB fields.
It is possible to have 85,000 items in a WHERE clause, and parentheses can be nested on 1,200 levels.

What is the maximum number of users?

The maximum number of simultaneous connections that a database can handle is limited by the max_connections variable.

However, this value must be chosen carefully: your operating system has its own limitations on network connections and processes that can be active simultaneously.

What is the maximum number of databases and tables?

The only limit depends on the number of files you can store in a directory.

When a database is created, MySQL creates a directory where the tables will be stored. As a result, the number of bases is limited by the number of subdirectories that your system can handle inside the MySQL data directory.
For tables of type ISAM and MyISAM, each table in a database consists of 3 files.

So, the maximum number of tables you can set is limited by the number of files your operating system allows in a single directory, divided by tables. Other table types (BDB, InnoDB) typically use only one file per table.
Temporary data will also be written in this directory, for example during optimizations of tables.

However, when designing your database, avoid the profusion of thousands of tables, the performance would suffer.

What is the maximum number of columns in a table?

The answer is: at least 2000 columns. It is best to avoid tables with too many columns.

What is the maximum quantity of data I can store in one line?

The total size of all columns containing simple types (numbers, CHAR / VARCHAR, ENUM, etc.) is limited to approximately 65,000 bytes.

TEXT and BLOB fields only “consume” about 1 to 4 bytes per column.

What is the maximum number of indexes for a table?

A MyISAM table can have 64 indexes (32 before MySQL 4.1.2).

What is the maximum number of fields in an index?

An index can have 16 field names.

The maximum length of an index (the all-values length combined) is 1000 bytes for MyISAM tables (500 bytes before MySQL 4.1.2).
The maximum length of a key in InnoDB is 1024 bytes.

What is the maximum length of a table, index, or field name?

64 characters. Aliases can be up to 500 characters in length.