Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Limits on Table Column Count and Row Size

C.10.4 Limits on Table Column Count and Row Size

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors.

  • Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

    The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. For example, utf8mb3 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8mb3 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.

    Storage for variable-length columns includes length bytes, which are assessed against the row size. For example, a VARCHAR(255) CHARACTER SET utf8mb3 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.

    BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.

    Declaring columns NULL can reduce the maximum number of columns permitted. For MyISAM 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 maximum row length in bytes can be calculated as follows:

    row length = 1
                 + (sum of column lengths)
                 + (number of NULL columns + delete_flag + 7)/8
                 + (number of variable-length columns)
    

    delete_flag is 1 for tables with static row format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header. For information about MyISAM table formats, see Section 15.3.3, “MyISAM Table Storage Formats”.

    For InnoDB tables, storage size is the same for NULL and NOT NULL columns, so the preceding calculations do not apply.

    The following 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 = MyISAM CHARACTER SET latin1;
    Query OK, 0 rows affected (0.02 sec)
    

    The following statement to create table t2 fails because the columns are NULL and MyISAM requires additional space that causes the row size to exceed 65,535 bytes:

    mysql> CREATE TABLE t2
        -> (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. You have to change some
    columns to TEXT or BLOBs
    

    The following statement to create table t3 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 t3
        -> (c1 VARCHAR(65535) NOT 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. You have to change some
    columns to TEXT or BLOBs
    

    Reducing the column length to 65,533 or less permits the statement to succeed.

  • Individual storage engines might impose additional restrictions that limit table column count. Examples:

    • InnoDB permits up to 1000 columns.

    • InnoDB restricts row size to something less than half a database page (approximately 8000 bytes), not including VARBINARY, VARCHAR, BLOB, or TEXT columns. For more information, see Section 14.11.8, “Limits on InnoDB Tables”.

    • Different InnoDB storage formats (COMPRESSED, REDUNDANT) use different amounts of page header and trailer data, which affects the amount of storage available for rows.

    • With innodb_strict_mode disabled, creating a table that uses the REDUNDANT or COMPACT row format succeeds with a warning if the defined columns exceed the maximum row size:

      | Warning |  139 | Row size too large (> 8123). 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.
      

      The DYNAMIC and COMPRESSED row formats are more restrictive in this regard. With innodb_strict_mode disabled, creating a table that uses the DYNAMIC or COMPRESSED row format fails with an error if the defined columns exceed the maximum row size:

      ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
      In current row format, BLOB prefix of 0 bytes is stored inline.
  • 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. For more information, see Section C.10.5, “Limits Imposed by .frm File Structure”.


User Comments
  Posted by Rick James on May 9, 2015
No, there is not really a row-size limit of 64KB. However, you may need to use MEDIUMTEXT or MEDIUMBLOB (etc) to get beyond 64KB.

Also, the comment about "blob and text ... contents stored separately" is quite incomplete, and Engine-dependent. The details about such are found on other pages. Within InnoDB, there are multiple flavors of "stored separately".
Sign Up Login You must be logged in to post a comment.