A list of the Limits in MySQL
Some of these are "hard limits" meaning that they are compiled into the code.
Some can be changed, with varing degrees of difficulty, by some setting.
A few of the values given here (or not given) may have been smaller in versions 5.0, 4.1, and older.
Nearly all the limits are so high that you will encounter other problems before
falling prey to the actual limit. I have peppered the limits with practical advice.
InnoDB-specific Limits
⚈ 64TB per InnoDB tablespace (ibdata1 or .ibd). Hence, a non-PARTITIONed table is limited to 64TB of data + indexes. A PARTITIONed table has one tablespace per PARTITION. With the non-default innodb_page_size=65536 (5.7.x), 256TB. (What happens in 8.0 is up in the air at this writing.)
⚈ Row < 4GB total
⚈ Row < 8KB in the block (assuming block size = 16KB), with long VARCHAR/TEXT/VARBINARY/BLOB fields moved to another area. It is possible, but unlikely, to design a table that won't compile. For other block sizes except 64KB, the limit is just under 1/2 block; 64KB blocks limit rows to 16KB. (cf innodb_page_size)
⚈ Row < half the block size (if not default); abs limit of 16KB. This excludes TEXTs and BLOBs.
⚈ Columns per table < 1000 (1017 in 5.6.9, InnoDB)
⚈ A BLOB must be less than one-tenth the size of the log (innodb_log_file_size * innodb_log_files_in_group).
⚈ innodb_log_file_size * innodb_log_files_in_group < 4GB before 5.6.3; 512GB after.
⚈ innodb_buffer_pool_instances -- 64 (new in 5.5.4). Recommend buffer_pool_size / 1G, up to 16.
⚈ innodb_log_file_size should be 10 times larger than the largest BLOB data size found (max_allowed_packet).
⚈ A transaction will timeout after 50 seconds (change via innodb_lock_wait_timeout; fixing code would be better)
⚈ 1023 concurrent data-modifying transactions (128K in 5.5.4; 96K in 5.7.2)
⚈ 4B tables. The OS is likely to croak long before that.
⚈ Max key length: 3500
⚈ FAT16 and FAT32 filesystems limit files (hence tables) to 4GB. NTFS is limited to 2TB? or 16GB? PARTITIONing would be a kludgy workaround.
The number of rows is effectively 'unlimited'. More discussion:
StackOverflow
(Index-related items are later.)
MyISAM-specific Limits
Please do not use MyISAM in future products. It is being actively phased out by Oracle.
(It continues to be available in MariaDB.)
⚈ Default limit on MyISAM table size of 2**48 (256TB) bytes. Hard limit of 2**56 bytes for a table. (See myisam_data_pointer_size (defaults to 6 bytes = 48 bits) and MAX_ROWS and AVG_ROW_LENGTH and CREATE TABLE)
⚈ Do not bother with FIXED versus DYNAMIC.
⚈ key_buffer_size (cache for MyISAM indexes) -- Unlimited; before 5.0.52/5.1.23 there was a hard limit: 4GB (even on 64-bit machines)
⚈ A row, excluding TEXTs and BLOBs, is limited to 64KB.
⚈ The .MYI (index) size is controlled by the undocumented myisam_index_pointer_size, which defaults to 1 byte less than the data_pointer.
⚈ 2598(?) columns
MEMORY-specific Limits
⚈ Each MEMORY table is limited by max_heap_table_size, which can be set just before the CREATE TABLE.
32-bit OS or 32-bit version of MySQL
It is amazing, but some people are installing 32-bit versions even in 2017.
(Usually it is an accident.)
⚈ Tables can be bigger than 4GB, but ram-related settings cannot be.
⚈ Most or all SHOW STATUS values wrap at 4G.
⚈ However...
"Table is full"
Datatypes
⚈ Integer (BIGINT): 0-2**64 (UNSIGNED) or +/- 2**63 (SIGNED)
⚈ Max size for a column of a table -- 4GB (LONGTEXT, LONGBLOB). Other limits may prevent you from achieving this.
⚈ VARCHAR/VARBINARY -- essentially unlimited (beyond 64KB it is turned into MEDIUMTEXT/etc)
⚈ ENUM -- 64K options
⚈ SET -- 64 options
⚈ DECIMAL(m,n) -- m<=65, n<=30 digits (plus some issues in 5.0.3 to 5.0.6)
⚈ CHARACTER SET utf8 is limited to 3-byte utf8 codes. utf8mb4 (new in 5.5.3) allows 4-byte codes.
⚈ FLOAT -- about 7 significant digits; DOUBLE -- about 16 significant digits. Do not use for money
⚈ Column, table, and database names -- limited to utf8 (not utf8mb4) (even in 8.0)
⚈ Bit operators (&, |, etc), through 5.7, apply only to SETs and INTs (of various sizes). The 64-bit limit is lifted in 8.0, allowing operators on BLOB.
General limits
⚈ GRANTs: Unlimited except by MyISAM table limits and memory limits. (Changing in 8.0 with re-implementation in InnoDB.)
⚈ 16K connnections (hard limit); memory may be stressed long before that
⚈ Names (db/table/view/sp/column) -- 64 utf8 characters (not utf8mb4)
⚈ .frm (schema definition) -- 64KB; this impacts column names and count, ENUM/SET options. 8.0.0 removes .frm files and the size limit.
⚈ Query Cache -- probably limited to 4GB; efficiency degrades with size; suggest no bigger than 50MB. (Amazon Aurora can handle bigger QC size.)
⚈ 1024 (pre 5.6.7) / 8192 PARTITIONs per table
⚈ Database and table names are case sensitive on Unix; case insensitive on Windows.
⚈ The internal size of a table row is limited to 65,535 bytes. However, you are likely to hit other limits first. The limit may apply only to VARCHAR and VARBINARY. Hence, when you have lots of big VARCHARs, changing them to TEXTs is a simply and effective fix.
SELECTs
⚈ JOINs, including implicit ones in Views: 61
⚈ The number of values in the IN list is limited by max_allowed_packet bytes. (100K items is likely to be stressful)
⚈ Length of SELECT (and other statements): probably limited to max_allowed_packet, which is cannot be more than 1GB.
⚈ Temp tables created because of JOIN (etc): This may become a hidden MyISAM table and be subject to the default MyISAM limits.
⚈ GROUP_CONCAT(): 1024 bytes?/characters? (change via group_concat_max_len)
⚈ optimizer_search_depth defaults to 62 -- Using 1 instead may be useful when join same-size tables and EAV schemas
INDEX Related
⚈ Index size -- unclear what the limit is (some combo of number of columns and max byte size of each col). You will get an error message if you exceed it.
⚈ Maximum length for entire key: 3072 bytes (3500 for MariaDB 10.1.48)
⚈ Index "prefix": MyISAM: 1000, InnoDB 767 (see below);
⚈ InnoDB with utf8mb4: VARCHAR(191) per column (see '767' below);
⚈ 16 columns in a composite index (MariaDB 5.5.20 allows 32)
⚈ SPATIAL -- Supported in MyISAM; Datatype supported in InnoDB; Indexing supported in InnoDB beginning with 5.7.5
⚈ 64 secondary indexes (InnoDB)
767 Limit in InnoDB Indexes
Re: #1071 - Specified key was too long; max key length is 767 bytes
This problem exists before the limit was raised in 5.7.7 (MariaDB 10.2.2?).
If you are hitting the limit because of
trying to use CHARACTER SET utf8mb4. Then do one of the following (each has a drawback) to avoid the error:
⚈ Upgrade to 5.7.7/10.2.2 (or later) for a 3072 byte limit;
⚈ Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters (unlikely?);
⚈ ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese;
⚈ Use a "prefix" index -- you lose some of the performance benefits.
⚈ Or... Stay with 5.6/5.5/10.1 but perform 4 steps to raise the limit to 3072 bytes:
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- (or COMPRESSED)
Note: In later versions, these are deprecated (5.7 and 10.2?) because they are no longer necessary: innodb_file_format, innodb_file_format_check, innodb_file_format_max and innodb_large_prefix. If you get an error to that effect, simply don't bother setting them. They are actually removed in 8.0 / 10.4(?)
Here is a way to generate the ALTER...CONVERT TO queries to copy & paste:
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name,
'` CONVERT TO CHARACTER SET utf8;') -- see note
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema',
'performance_schema', 'sys');
Note: If you are going to, instead of away from, utf8mb4, change the charset accordingly.
MyISAM's FULLTEXT
⚈ ft_min_word_len defaults to 4; hard to change
⚈ if 50% or more of rows contain a word, those rows are not found IN NATURAL LANGUAGE MODE (the default)
⚈ English stopwords are not indexed
InnoDB's FULLTEXT
⚈ not available until 5.6
⚈ innodb_ft_min_token_size defaults to 3
⚈ SHOW VARIABLES LIKE 'innodb_ft%'
⚈ No 50% rule
⚈ AGAINST('+ab +xyz' IN BOOLEAN MODE) fails to locate a row "ab xyz", presumably because ab is too short. Workaround: don't add + to words shorter than innodb_ft_min_token_size.
More caveats
"Practical" Limits
For performance reasons, these are advised:
⚈ Number of databases or tables: OS limited, but over 10K causes performance problems.
⚈ Table cache tunables: table_open_cache, open_files_limit, table_open_cache_instances, innodb_open_files -- typically 'hundreds' is good
⚈ Under 50 PARTITIONs (may be remedied by 'native partitions' in 5.7.6)
⚈ query_cache_size under 50M (or off) (Exception: Amazon Aurora)
⚈ under 5 columns in a 'composite' index
⚈ under 5 indexes
⚈ INT UNSIGNED for AUTO_INCREMENT (do you really need > 4 billion, hence BIGINT?)
⚈ Use 100-1000 rows per batch INSERT or DELETE
⚈ innodb_flush_log_at_trx_commit = 1 for safety; = 2 for speed or clustering (Galera, PXC, etc)
Some observations:
⚈ MySQL can do thousand(s) of 'simple' queries per second
⚈ Ordinary disks (HDD) cannot do more than about 100 operations/sec
⚈ MySQL 'never' uses more than one CPU core per connection (even with UNION or PARTITION)
Postlog
References:
5.7 Limits chapter
FAT32
Zaitsev's blog on query_cache_size
MariaDB
Written -- Aug, 2008
on forum
Refreshed and posted here -- June, 2013;
Revised Sep, 2013;
Revised Nov, 2014;
Revised May, 2015;
Belatedly add solution for 767: May, 2017;
Refresh Sep, 2017, several additions, more 8.0 notes;
FULLTEXT clarifications June, 2018
The tips in this article apply to MySQL, MariaDB and Percona.
OS Limits
Partition Maintenance (DROP+REORG) for time series (includes list of PARTITION uses)
Big DELETEs - how to optimize -- and other chunking advice, plus a use for PARTITIONing
Chunking lengthy DELETE/UPDATE/etc.
Data Warehouse techniques:
Data Warehouse Overview
Summary Tables
High speed ingestion
Bulk Normalization
Schema and code design for large Sensor database
Entity-Attribute-Value (EAV) -- a common, poorly performing, design pattern; plus an alternative
Lat/Lng search to Find the nearest 10 pizza parlors
Lat/Long representation choices
Pagination, not with OFFSET, LIMIT
Techniques on efficiently finding a random row (On beyond ORDER BY RAND())
GUID/UUID Performance (type 1 only)
IP Range Table Performance -- or other disjoint ranges
Alter of a Huge table -- Mostly obviated by 5.6
Efficient List of Latest 10 news articles
Build and execute a Pivot SELECT (showing rows as columns)
(Groupwise Max): Efficiently find largest row(s) for each group
Other Tips, Tuning, Debugging, Optimizations, etc...
Rick's RoTs (Rules of Thumb -- lots of tips)
Datatypes and building a good schema
Memory Allocation (caching, etc)
Character Set and Collation problem solver
Trouble with UTF-8
If you want case folding, but accent sensitivity, please file a request at https://bugs.mysql.com .
Python tips,
PHP tips,
other language tips
utf8 Collations
utf8mb4 Collations on 8.0
Converting from MyISAM to InnoDB -- includes differences between them
Compound INDEXes plus other insights into the mysteries of INDEXing
Cookbook for Creating Indexes
Many-to-many mapping table
Handler counts
wp_postmeta
UNION+OFFSET
MySQL Limits -- built-in hard limits
767-byte INDEX limit
Galera, tips on converting to (Percona XtraDB Cluster, MariaDB 10, or manually installed)
5.7's Query Rewrite -- perhaps 5.7's best perf gain, at least for this forum's users
Analyze MySQL Performance
Analyze VARIABLEs and GLOBAL STATUS
Analyze SlowLog
My slides from conferences
MiniFest 2021 - Rick James & Daniel Black - Answering on Stack Overflow(+comments) - MariaDB Frontlines
Percona Live 4/2017 - Rick's RoTs (Rules of Thumb) - MySQL/MariaDB
Percona Live 4/2017 - Index Cookbook - MySQL/MariaDB
Percona Live 9/2015 - PARTITIONing - MySQL/MariaDB
Contact me via LinkedIn; be sure to include a brief teaser in the Invite request:
