Limits

Table of Contents

A list of the Limits in MySQL
InnoDB-specific Limits
MyISAM-specific Limits
MEMORY-specific Limits
32-bit OS or 32-bit version of MySQL
Datatypes
General limits
SELECTs
INDEX Related
MyISAM's FULLTEXT
InnoDB's FULLTEXT
"Practical" Limits
Postlog
Brought to you by Rick James

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.

InnoDB-specific Limits


    ⚈  64TB per InnoDB tablespace (ibdata1 or .ibd). But 32TB limit per table. 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.
    ⚈  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.
    ⚈  Row < half the block size (if not default); abs limit of 16KB
    ⚈  Columns per table < 1000 (1017 in 5.6.9, InnoDB); 2598(?) for MyISAM
    ⚈  1023 concurrent transactions that have created undo records by modifying data
    ⚈  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 (pre 5.6.3) or 512GB
    ⚈  innodb_buffer_pool_instances -- 64 (new in 5.5.4)
    ⚈  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)
    ⚈  4B tables
    ⚈  Max key length: 3500

MyISAM-specific Limits


    ⚈  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 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)

MEMORY-specific Limits


    ⚈  Each MEMORY table is limited by max_heap_table_size, which can be set just before the CREATE.

32-bit OS or 32-bit version of MySQL


    ⚈  Tables can be bigger than 4GB, but ram-related settings cannot be.
    ⚈  Most or all SHOW STATUS values wrap at 4G.

Datatypes


    ⚈  Integer (BIGINT): 0-2**64 (UNSIGNED) or +/- 2**63 (SIGNED)
    ⚈  One column of 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 -- 65 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.

General limits


    ⚈  GRANTs: Unlimited except by MyISAM table limits and memory limits.
    ⚈  16K connnections (hard limit); memory may be stressed long before that
    ⚈  Names (db/table/view/sp/column) -- 64 utf8 characters
    ⚈  .frm (schema definition) -- 64KB; this impacts column names and count, ENUM/SET options
    ⚈  Query Cache -- probably limited to 4GB; efficiency degrades with size; suggest no bigger than 50MB.
    ⚈  1024 (pre 5.6.7) / 8192 PARTITIONs per table
    ⚈  Database and table _names_ are case sensitive on Unix; case insensitive on Windows.

SELECTs


    ⚈  JOINs, including implicit ones in Views: 61
    ⚈  The number of values in the IN list is limited by max_allowed_packet value. (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 -- 1 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
    ⚈  Index "prefix": MyISAM: 1000, InnoDB: 767 (VARCHAR(255) with utf8, VARCHAR(191) with utf8mb4)
    ⚈  With 5.7.7, max byte size for each column: 3072 (Previously usable via innodb_file_format=Barracuda + innodb_file_per_table=ON + innodb_large_prefix=ON + Row_format = DYNAMIC or COMPRESSED)
    ⚈  InnoDB with utf8mb4: VARCHAR(191) per column; possible to work-around, see innodb_large_prefix
    ⚈  16 columns in an index
    ⚈  SPATIAL -- Supported in MyISAM; Datatype supported in InnoDB; Indexing supported in InnoDB beginning with 5.7.5)
    ⚈  64 secondary indexes (InnoDB)

MyISAM's FULLTEXT

    ⚈  ft_min_word_len defaults to 4; hard to change
    ⚈  if more than 50% of rows contain a word, it is not indexed
    ⚈  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
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
    ⚈  under 5 columns in an 'compound' 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 Galera
Some observations:
    ⚈  MySQL can do thousand(s) of 'simple' queries per second
    ⚈  Ordinary disks cannot do more than 100 operations/sec
    ⚈  MySQL 'never' uses more than one CPU core per connection (even with PARTITIONs)

Postlog

References:
5.7 Limits chapter
Limits on InnoDB Tables

Written -- Aug, 2008 on forum
Refreshed and posted here -- June, 2013; Revised Sep, 2013; Revised Nov, 2014; Revised May, 2015

The tips in this article apply to MySQL, MariaDB and Percona.

OS Limits


Contact me by posting a question at MySQL Forums :: Performance
-- Rick James

MySQL Documents by Rick James

HowTo Techniques for Optimizing Tough Tasks:

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:
    Overview   Summary Tables   High speed ingestion  
Entity-Attribute-Value -- a common, poorly performing, design pattern (EAV); plus an alternative
Find the nearest 10 pizza parlors -- efficient searching on Latitude + Longitude (another PARITION use)
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
Rollup Unique User Counts
Alter of a Huge table -- Mostly obviated by 5.6
Latest 10 news articles -- how to optimize the schema and code for such
Build and execute a "Pivot" SELECT (showing rows as columns)
Find largest row for each group ("groupwise max")

Other Tips, Tuning, Debugging, Optimizations, etc...

Rick's RoTs (Rules of Thumb -- lots of tips)
Memory Allocation (caching, etc)
Character Set and Collation problem solver
    Trouble with UTF-8
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
MySQL Limits -- built-in hard limits
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
Best of MySQL Forum -- index of lots of tips, discussions, etc

View Rick James's profile on LinkedIn