Menu:
column-count-limit:: Limits on Table Column Count and Row Size
In your role as a database designer, look for the most efficient way to organize your schemas, tables, and columns. As when tuning application code, you minimize I/O, keep related items together, and plan ahead so that performance stays high as the data volume increases. Starting with an efficient database design makes it easier for team members to write high-performing application code, and makes the database likely to endure as applications evolve and are rewritten.
File: manual.info.tmp, Node: data-size, Next: optimize-data-types, Prev: optimizing-database-structure, Up: optimizing-database-structure
Design your tables to minimize their space on the disk. This can result in huge improvements by reducing the amount of data written to and read from disk. Smaller tables normally require less main memory while their contents are being actively processed during query execution. Any space reduction for table data also results in smaller indexes that can be processed faster.
MySQL supports many different storage engines (table types) and row formats. For each table, you can decide which storage and indexing method to use. Choosing the proper table format for your application can give you a big performance gain. See note innodb-storage-engine::, and note storage-engines::.
You can get better performance for a table and minimize storage space by using the techniques listed here:
*note data-size-table-columns::
*note data-size-row-format::
*note data-size-indexes::
*note data-size-joins::
*note data-size-normalization::
Table Columns
Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. note 'MEDIUMINT': integer-types. is often a better choice than note 'INT': integer-types. because a *note 'MEDIUMINT': integer-types. column uses 25% less space.
Declare columns to be 'NOT NULL' if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is 'NULL'. You also save some storage space, one bit per column. If you really need 'NULL' values in your tables, use them. Just avoid the default setting that allows 'NULL' values in every column.
Row Format
'InnoDB' tables are created using the 'DYNAMIC' row format by default. To use a row format other than 'DYNAMIC', configure 'innodb_default_row_format', or specify the 'ROW_FORMAT' option explicitly in a note 'CREATE TABLE': create-table. or note 'ALTER TABLE': alter-table. statement.
The compact family of row formats, which includes 'COMPACT', 'DYNAMIC', and 'COMPRESSED', decreases row storage space at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed it is likely to be faster. If it is a rare case that is limited by CPU speed, it might be slower.
The compact family of row formats also optimizes note 'CHAR': char. column storage when using a variable-length character set such as 'utf8mb3' or 'utf8mb4'. With 'ROW_FORMAT=REDUNDANT', 'CHAR(N)' occupies N x the maximum byte length of the character set. Many languages can be written primarily using single-byte 'utf8' characters, so a fixed storage length often wastes space. With the compact family of rows formats, 'InnoDB' allocates a variable amount of storage in the range of N to N x the maximum byte length of the character set for these columns by stripping trailing spaces. The minimum storage length is N bytes to facilitate in-place updates in typical cases. For more information, see note innodb-row-format::.
To minimize space even further by storing table data in compressed form, specify 'ROW_FORMAT=COMPRESSED' when creating 'InnoDB' tables, or run the *note 'myisampack': myisampack. command on an existing 'MyISAM' table. ('InnoDB' compressed tables are readable and writable, while 'MyISAM' compressed tables are read-only.)
For 'MyISAM' tables, if you do not have any variable-length columns (note 'VARCHAR': char, note 'TEXT': blob, or note 'BLOB': blob. columns), a fixed-size row format is used. This is faster but may waste some space. See note myisam-table-formats::. You can hint that you want to have fixed length rows even if you have note 'VARCHAR': char. columns with the note 'CREATE TABLE': create-table. option 'ROW_FORMAT=FIXED'.
Indexes
The primary index of a table should be as short as possible. This makes identification of each row easy and efficient. For 'InnoDB' tables, the primary key columns are duplicated in each secondary index entry, so a short primary key saves considerable space if you have many secondary indexes.
Create only the indexes that you need to improve query performance. Indexes are good for retrieval, but slow down insert and update operations. If you access a table mostly by searching on a combination of columns, create a single composite index on them rather than a separate index for each column. The first part of the index should be the column most used. If you always use many columns when selecting from the table, the first column in the index should be the one with the most duplicates, to obtain better compression of the index.
If it is very likely that a long string column has a unique prefix on the first number of characters, it is better to index only this prefix, using MySQL's support for creating an index on the leftmost part of the column (see note create-index::). Shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache, and thus fewer disk seeks. See note server-configuration::.
Joins
In some circumstances, it can be beneficial to split into two a table that is scanned very often. This is especially true if it is a dynamic-format table and it is possible to use a smaller static format table that can be used to find the relevant rows when scanning the table.
Declare columns with identical information in different tables with identical data types, to speed up joins based on the corresponding columns.
Keep column names simple, so that you can use the same name across different tables and simplify join queries. For example, in a table named 'customer', use a column name of 'name' instead of 'customer_name'. To make your names portable to other SQL servers, consider keeping them shorter than 18 characters.
Normalization
Normally, try to keep all data nonredundant (observing what is referred to in database theory as third normal form). Instead of repeating lengthy values such as names and addresses, assign them unique IDs, repeat these IDs as needed across multiple smaller tables, and join the tables in queries by referencing the IDs in the join clause.
If speed is more important than disk space and the maintenance costs of keeping multiple copies of data, for example in a business intelligence scenario where you analyze all the data from large tables, you can relax the normalization rules, duplicating information or creating summary tables to gain more speed.
File: manual.info.tmp, Node: optimize-data-types, Next: optimize-multi-tables, Prev: data-size, Up: optimizing-database-structure
Menu:
procedure-analyse:: Using PROCEDURE ANALYSE
File: manual.info.tmp, Node: optimize-numeric, Next: optimize-character, Prev: optimize-data-types, Up: optimize-data-types
8.4.2.1 Optimizing for Numeric Data ...................................
For unique IDs or other values that can be represented as either strings or numbers, prefer numeric columns to string columns. Since large numeric values can be stored in fewer bytes than the corresponding strings, it is faster and takes less memory to transfer and compare them.
If you are using numeric data, it is faster in many cases to access information from a database (using a live connection) than to access a text file. Information in the database is likely to be stored in a more compact format than in the text file, so accessing it involves fewer disk accesses. You also save code in your application because you can avoid parsing the text file to find line and column boundaries.
File: manual.info.tmp, Node: optimize-character, Next: optimize-blob, Prev: optimize-numeric, Up: optimize-data-types
8.4.2.2 Optimizing for Character and String Types .................................................
For character and string columns, follow these guidelines:
Use binary collation order for fast comparison and sort operations, when you do not need language-specific collation features. You can use the 'BINARY' operator to use binary collation within a particular query.
When comparing values from different columns, declare those columns with the same character set and collation wherever possible, to avoid string conversions while running the query.
For column values less than 8KB in size, use binary 'VARCHAR' instead of 'BLOB'. The 'GROUP BY' and 'ORDER BY' clauses can generate temporary tables, and these temporary tables can use the 'MEMORY' storage engine if the original table does not contain any 'BLOB' columns.
If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table and using join queries with a foreign key when necessary. When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block. Such compact tables reduce disk I/O and memory usage for common queries.
When you use a randomly generated value as a primary key in an 'InnoDB' table, prefix it with an ascending value such as the current date and time if possible. When consecutive primary values are physically stored near each other, 'InnoDB' can insert and retrieve them faster.
See *note optimize-numeric:: for reasons why a numeric column is usually preferable to an equivalent string column.
File: manual.info.tmp, Node: optimize-blob, Next: procedure-analyse, Prev: optimize-character, Up: optimize-data-types
8.4.2.3 Optimizing for BLOB Types .................................
When storing a large blob containing textual data, consider compressing it first. Do not use this technique when the entire table is compressed by 'InnoDB' or 'MyISAM'.
For a table with several columns, to reduce memory requirements for queries that do not use the BLOB column, consider splitting the BLOB column into a separate table and referencing it with a join query when needed.
Since the performance requirements to retrieve and display a BLOB value might be very different from other data types, you could put the BLOB-specific table on a different storage device or even a separate database instance. For example, to retrieve a BLOB might require a large sequential disk read that is better suited to a traditional hard drive than to an SSD device.
See *note optimize-character:: for reasons why a binary 'VARCHAR' column is sometimes preferable to an equivalent BLOB column.
Rather than testing for equality against a very long text string, you can store a hash of the column value in a separate column, index that column, and test the hashed value in queries. (Use the 'MD5()' or 'CRC32()' function to produce the hash value.) Since hash functions can produce duplicate results for different inputs, you still include a clause 'AND BLOB_COLUMN = LONG_STRING_VALUE' in the query to guard against false matches; the performance benefit comes from the smaller, easily scanned index for the hashed values.
File: manual.info.tmp, Node: procedure-analyse, Prev: optimize-blob, Up: optimize-data-types
8.4.2.4 Using PROCEDURE ANALYSE ...............................
'ANALYSE([MAX_ELEMENTS[,MAX_MEMORY]])'
Note:
'PROCEDURE ANALYSE()' is deprecated as of MySQL 5.7.18, and is removed in MySQL 8.0.
'ANALYSE()' examines the result from a query and returns an analysis of the results that suggests optimal data types for each column that may help reduce table sizes. To obtain this analysis, append 'PROCEDURE ANALYSE' to the end of a *note 'SELECT': select. statement:
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([MAX_ELEMENTS,[MAX_MEMORY]])
For example:
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
The results show some statistics for the values returned by the query, and propose an optimal data type for the columns. This can be helpful for checking your existing tables, or after importing new data. You may need to try different settings for the arguments so that 'PROCEDURE ANALYSE()' does not suggest the *note 'ENUM': enum. data type when it is not appropriate.
The arguments are optional and are used as follows:
MAX_ELEMENTS (default 256) is the maximum number of distinct values that 'ANALYSE()' notices per column. This is used by 'ANALYSE()' to check whether the optimal data type should be of type note 'ENUM': enum.; if there are more than MAX_ELEMENTS distinct values, then note 'ENUM': enum. is not a suggested type.
MAX_MEMORY (default 8192) is the maximum amount of memory that 'ANALYSE()' should allocate per column while trying to find all distinct values.
A 'PROCEDURE' clause is not permitted in a *note 'UNION': union. statement.
File: manual.info.tmp, Node: optimize-multi-tables, Next: internal-temporary-tables, Prev: optimize-data-types, Up: optimizing-database-structure
Menu:
creating-many-tables:: Disadvantages of Creating Many Tables in the Same Database
Some techniques for keeping individual queries fast involve splitting data across many tables. When the number of tables runs into the thousands or even millions, the overhead of dealing with all these tables becomes a new performance consideration.
File: manual.info.tmp, Node: table-cache, Next: creating-many-tables, Prev: optimize-multi-tables, Up: optimize-multi-tables
8.4.3.1 How MySQL Opens and Closes Tables .........................................
When you execute a *note 'mysqladmin status': mysqladmin. command, you should see something like this:
Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12
The 'Open tables' value of 12 can be somewhat puzzling if you have fewer than 12 tables.
MySQL is multithreaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. This uses additional memory but normally increases performance. With 'MyISAM' tables, one extra file descriptor is required for the data file for each client that has the table open. (By contrast, the index file descriptor is shared between all sessions.)
The 'table_open_cache' and 'max_connections' system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. Many operating systems permit you to increase the open-files limit, although the method varies widely from system to system. Consult your operating system documentation to determine whether it is possible to increase the limit and how to do so.
'table_open_cache' is related to 'max_connections'. For example, for 200 concurrent running connections, specify a table cache size of at least '200 * N', where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.
Make sure that your operating system can handle the number of open file descriptors implied by the 'table_open_cache' setting. If 'table_open_cache' is set too high, MySQL may run out of file descriptors and exhibit symptoms such as refusing connections or failing to perform queries.
Also take into account that the 'MyISAM' storage engine needs two file descriptors for each unique open table. For a partitioned 'MyISAM' table, two file descriptors are required for each partition of the opened table. (When 'MyISAM' opens a partitioned table, it opens every partition of this table, whether or not a given partition is actually used. See note partitioning-limitations-myisam-file-descriptors::.) To increase the number of file descriptors available to MySQL, set the 'open_files_limit' system variable. See note not-enough-file-handles::.
The cache of open tables is kept at a level of 'table_open_cache' entries. The server autosizes the cache size at startup. To set the size explicitly, set the 'table_open_cache' system variable at startup. MySQL may temporarily open more tables than this to execute queries, as described later in this section.
MySQL closes an unused table and removes it from the table cache under the following circumstances:
When the cache is full and a thread tries to open a table that is not in the cache.
When the cache contains more than 'table_open_cache' entries and a table in the cache is no longer being used by any threads.
When a table-flushing operation occurs. This happens when someone issues a 'FLUSH TABLES' statement or executes a note 'mysqladmin flush-tables': mysqladmin. or note 'mysqladmin refresh': mysqladmin. command.
When the table cache fills up, the server uses the following procedure to locate a cache entry to use:
Tables not currently in use are released, beginning with the table least recently used.
If a new table must be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary. When the cache is in a temporarily extended state and a table goes from a used to unused state, the table is closed and released from the cache.
A 'MyISAM' table is opened for each concurrent access. This means the table needs to be opened twice if two threads access the same table or if a thread accesses the table twice in the same query (for example, by joining the table to itself). Each concurrent open requires an entry in the table cache. The first open of any 'MyISAM' table takes two file descriptors: one for the data file and one for the index file. Each additional use of the table takes only one file descriptor for the data file. The index file descriptor is shared among all threads.
If you are opening a table with the 'HANDLER TBL_NAME OPEN' statement, a dedicated table object is allocated for the thread. This table object is not shared by other threads and is not closed until the thread calls 'HANDLER TBL_NAME CLOSE' or the thread terminates. When this happens, the table is put back in the table cache (if the cache is not full). See *note handler::.
To determine whether your table cache is too small, check the 'Opened_tables' status variable, which indicates the number of table-opening operations since the server started:
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741 |
+---------------+-------+
If the value is very large or increases rapidly, even when you have not issued many 'FLUSH TABLES' statements, increase the 'table_open_cache' value at server startup.
File: manual.info.tmp, Node: creating-many-tables, Prev: table-cache, Up: optimize-multi-tables
8.4.3.2 Disadvantages of Creating Many Tables in the Same Database ..................................................................
If you have many 'MyISAM' tables in the same database directory, open, close, and create operations are slow. If you execute *note 'SELECT': select. statements on many different tables, there is a little overhead when the table cache is full, because for every table that has to be opened, another must be closed. You can reduce this overhead by increasing the number of entries permitted in the table cache.
File: manual.info.tmp, Node: internal-temporary-tables, Next: database-count-limit, Prev: optimize-multi-tables, Up: optimizing-database-structure
In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.
The server creates temporary tables under conditions such as these:
Evaluation of *note 'UNION': union. statements, with some exceptions described later.
Evaluation of some views, such those that use the 'TEMPTABLE' algorithm, *note 'UNION': union, or aggregation.
Evaluation of derived tables (see *note derived-tables::).
Tables created for subquery or semijoin materialization (see *note subquery-optimization::).
Evaluation of statements that contain an 'ORDER BY' clause and a different 'GROUP BY' clause, or for which the 'ORDER BY' or 'GROUP BY' contains columns from tables other than the first table in the join queue.
Evaluation of 'DISTINCT' combined with 'ORDER BY' may require a temporary table.
For queries that use the 'SQL_SMALL_RESULT' modifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
To evaluate note 'INSERT ... SELECT': insert-select. statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the note 'SELECT': select, then inserts those rows into the target table. See *note insert-select::.
Evaluation of multiple-table *note 'UPDATE': update. statements.
Evaluation of 'GROUP_CONCAT()' or 'COUNT(DISTINCT)' expressions.
To determine whether a statement requires a temporary table, use note 'EXPLAIN': explain. and check the 'Extra' column to see whether it says 'Using temporary' (see note using-explain::). 'EXPLAIN' does not necessarily say 'Using temporary' for derived or materialized temporary tables.
Some query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
Presence of a note 'BLOB': blob. or note 'TEXT': blob. column in the table. This includes user-defined variables having a string value because they are treated as note 'BLOB': blob. or note 'TEXT': blob. columns, depending on whether their value is a binary or nonbinary string, respectively.
Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the note 'SELECT': select. list, if note 'UNION': union. or *note 'UNION ALL': union. is used.
The note 'SHOW COLUMNS': show-columns. and note 'DESCRIBE': describe. statements use 'BLOB' as the type for some columns, thus the temporary table used for the results is an on-disk table.
The server does not use a temporary table for note 'UNION': union. statements that meet certain qualifications. Instead, it retains from temporary table creation only the data structures necessary to perform result column typecasting. The table is not fully instantiated and no rows are written to or read from it; rows are sent directly to the client. The result is reduced memory and disk requirements, and smaller delay before the first row is sent to the client because the server need not wait until the last query block is executed. note 'EXPLAIN': explain. and optimizer trace output reflects this execution strategy: The 'UNION RESULT' query block is not present because that block corresponds to the part that reads from the temporary table.
These conditions qualify a 'UNION' for evaluation without a temporary table:
The union is 'UNION ALL', not 'UNION' or 'UNION DISTINCT'.
There is no global 'ORDER BY' clause.
The union is not the top-level query block of an '{INSERT | REPLACE} ... SELECT ...' statement.
Internal Temporary Table Storage Engine
An internal temporary table can be held in memory and processed by the 'MEMORY' storage engine, or stored on disk by the 'InnoDB' or 'MyISAM' storage engine.
If an internal temporary table is created as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is defined by the 'tmp_table_size' or 'max_heap_table_size' value, whichever is smaller. This differs from 'MEMORY' tables explicitly created with *note 'CREATE TABLE': create-table. For such tables, only the 'max_heap_table_size' variable determines how large a table can grow, and there is no conversion to on-disk format.
The 'internal_tmp_disk_storage_engine' variable defines the storage engine the server uses to manage on-disk internal temporary tables. Permitted values are 'INNODB' (the default) and 'MYISAM'.
Note:
When using 'internal_tmp_disk_storage_engine=INNODB', queries that generate on-disk internal temporary tables that exceed *note 'InnoDB' row or column limits: innodb-limits. return 'Row size too large' or 'Too many columns' errors. The workaround is to set 'internal_tmp_disk_storage_engine' to 'MYISAM'.
When an internal temporary table is created in memory or on disk, the server increments the 'Created_tmp_tables' value. When an internal temporary table is created on disk, the server increments the 'Created_tmp_disk_tables' value. If too many internal temporary tables are created on disk, consider increasing the 'tmp_table_size' and 'max_heap_table_size' settings.
Internal Temporary Table Storage Format
In-memory temporary tables are managed by the 'MEMORY' storage engine, which uses fixed-length row format. 'VARCHAR' and 'VARBINARY' column values are padded to the maximum column length, in effect storing them as 'CHAR' and 'BINARY' columns.
On-disk temporary tables are managed by the 'InnoDB' or 'MyISAM' storage engine (depending on the 'internal_tmp_disk_storage_engine' setting). Both engines store temporary tables using dynamic-width row format. Columns take only as much storage as needed, which reduces disk I/O, space requirements, and processing time compared to on-disk tables that use fixed-length rows.
For statements that initially create an internal temporary table in memory, then convert it to an on-disk table, better performance might be achieved by skipping the conversion step and creating the table on disk to begin with. The 'big_tables' variable can be used to force disk storage of internal temporary tables.
File: manual.info.tmp, Node: database-count-limit, Next: table-size-limit, Prev: internal-temporary-tables, Up: optimizing-database-structure
MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.
MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. 'InnoDB' permits up to 4 billion tables.
File: manual.info.tmp, Node: table-size-limit, Next: column-count-limit, Prev: database-count-limit, Up: optimizing-database-structure
The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. For up-to-date information operating system file size limits, refer to the documentation specific to your operating system.
Windows users, please note that FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.
If you encounter a full-table error, there are several reasons why it might have occurred:
The disk might be full.
You are using 'InnoDB' tables and have run out of room in an 'InnoDB' tablespace file. The maximum tablespace size is also the maximum size for a table. For tablespace size limits, see *note innodb-limits::.
Generally, partitioning of tables into multiple tablespace files is recommended for tables larger than 1TB in size.
You have hit an operating system file size limit. For example, you are using 'MyISAM' tables on an operating system that supports files only up to 2GB in size and you have hit this limit for the data file or index file.
You are using a 'MyISAM' table and the space required for the table exceeds what is permitted by the internal pointer size. 'MyISAM' permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (256^7 − 1 bytes).
If you need a 'MyISAM' table that is larger than the default limit and your operating system supports large files, the note 'CREATE TABLE': create-table. statement supports 'AVG_ROW_LENGTH' and 'MAX_ROWS' options. See note create-table::. The server uses these options to determine how large a table to permit.
If the pointer size is too small for an existing table, you can change the options with note 'ALTER TABLE': alter-table. to increase a table's maximum permissible size. See note alter-table::.
ALTER TABLE TBL_NAME MAX_ROWS=1000000000 AVG_ROW_LENGTH=NNN;
You have to specify 'AVG_ROW_LENGTH' only for tables with note 'BLOB': blob. or note 'TEXT': blob. columns; in this case, MySQL cannot optimize the space required based only on the number of rows.
To change the default size limit for 'MyISAM' tables, set the 'myisam_data_pointer_size', which sets the number of bytes used for internal row pointers. The value is used to set the pointer size for new tables if you do not specify the 'MAX_ROWS' option. The value of 'myisam_data_pointer_size' can be from 2 to 7. For example, for tables that use the dynamic storage format, a value of 4 permits tables up to 4GB; a value of 6 permits tables up to 256TB. Tables that use the fixed storage format have a larger maximum data length. For storage format characteristics, see *note myisam-table-formats::.
You can check the maximum data and index sizes by using this statement:
SHOW TABLE STATUS FROM DB_NAME LIKE 'TBL_NAME';
You also can use note 'myisamchk -dv /path/to/table-index-file': myisamchk. See note show::, or *note myisamchk::.
Other ways to work around file-size limits for 'MyISAM' tables are as follows:
* If your large table is read only, you can use *note
'myisampack': myisampack. to compress it. *note 'myisampack':
myisampack. usually compresses a table by at least 50%, so you
can have, in effect, much bigger tables. *note 'myisampack':
myisampack. also can merge multiple tables into a single
table. See *note myisampack::.
* MySQL includes a 'MERGE' library that enables you to handle a
collection of 'MyISAM' tables that have identical structure as
a single 'MERGE' table. See *note merge-storage-engine::.
You are using the 'MEMORY' ('HEAP') storage engine; in this case you need to increase the value of the 'max_heap_table_size' system variable. See *note server-system-variables::.
File: manual.info.tmp, Node: column-count-limit, Prev: table-size-limit, Up: optimizing-database-structure
This section describes limits on the number of columns in tables and the size of individual rows.
*note column-count-limits::
*note row-size-limits::
Column Count Limits
MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors:
The maximum row size for a table constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. See *note row-size-limits::.
The storage requirements of individual columns constrain the number of columns that fit within a given maximum row size. Storage requirements for some data types depend on factors such as storage engine, storage format, and character set. See *note storage-requirements::.
Storage engines may impose additional restrictions that limit table column count. For example, note 'InnoDB': innodb-storage-engine. has a limit of 1017 columns per table. See note innodb-limits::. For information about other storage engines, see *note storage-engines::.
Each table has an '.frm' file that contains the table definition. The definition affects the content of this file in ways that may affect the number of columns permitted in the table. See *note limits-frm-file::.
Row Size Limits
The maximum row size for a given table is determined by several factors:
The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. note 'BLOB': blob. and note 'TEXT': blob. columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.
The maximum row size for an 'InnoDB' table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB 'innodb_page_size' settings. For example, the maximum row size is slightly less than 8KB for the default 16KB 'InnoDB' page size. For 64KB pages, the maximum row size is slightly less than 16KB. See *note innodb-limits::.
If a row containing variable-length columns exceeds the 'InnoDB' maximum row size, 'InnoDB' selects variable-length columns for external off-page storage until the row fits within the 'InnoDB' row size limit. The amount of data stored locally for variable-length columns that are stored off-page differs by row format. For more information, see *note innodb-row-format::.
Different storage formats use different amounts of page header and trailer data, which affects the amount of storage available for rows.
* For information about 'InnoDB' row formats, see *note
innodb-row-format::.
* For information about 'MyISAM' storage formats, see *note
myisam-table-formats::.
Row Size Limit Examples
The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following 'InnoDB' and 'MyISAM' examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs
In the following 'MyISAM' example, changing a column to note 'TEXT': blob. avoids the 65,535-byte row size limit and permits the operation to succeed because note 'BLOB': blob. and *note 'TEXT': blob. columns only contribute 9 to 12 bytes toward the row size.
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)
The operation succeeds for an 'InnoDB' table because changing a column to *note 'TEXT': blob. avoids the MySQL 65,535-byte row size limit, and 'InnoDB' off-page storage of variable-length columns avoids the 'InnoDB' row size limit.
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)
Storage for variable-length columns includes length bytes, which are counted toward the row size. For example, a *note 'VARCHAR(255) CHARACTER SET utf8mb3': char. column takes two bytes to store the length of the value, so each value can take up to 767 bytes.
The statement to create table 't1' succeeds because the columns require 32,765 + 2 bytes and 32,766 + 2 bytes, which falls within the maximum row size of 65,535 bytes:
mysql> CREATE TABLE t1
(c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)
The statement to create table 't2' fails because, although the column length is within the maximum length of 65,535 bytes, two additional bytes are required to record the length, which causes the row size to exceed 65,535 bytes:
mysql> CREATE TABLE t2
(c1 VARCHAR(65535) NOT NULL)
ENGINE = InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs
Reducing the column length to 65,533 or less permits the statement to succeed.
mysql> CREATE TABLE t2
(c1 VARCHAR(65533) NOT NULL)
ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)
For *note 'MyISAM': myisam-storage-engine. tables, 'NULL' columns require additional space in the row to record whether their values are 'NULL'. Each 'NULL' column takes one bit extra, rounded up to the nearest byte.
The statement to create table 't3' fails because *note 'MyISAM': myisam-storage-engine. requires space for 'NULL' columns in addition to the space required for variable-length column length bytes, causing the row size to exceed 65,535 bytes:
mysql> CREATE TABLE t3
(c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs
For information about note 'InnoDB': innodb-storage-engine. 'NULL' column storage, see note innodb-row-format::.
'InnoDB' restricts row size (for data stored locally within the database page) to slightly less than half a database page for 4KB, 8KB, 16KB, and 32KB 'innodb_page_size' settings, and to slightly less than 16KB for 64KB pages.
The statement to create table 't4' fails because the defined columns exceed the row size limit for a 16KB 'InnoDB' page.
mysql> CREATE TABLE t4 (
c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using
ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768
bytes is stored inline.
File: manual.info.tmp, Node: optimizing-innodb, Next: optimizing-myisam, Prev: optimizing-database-structure, Up: optimization