Why Are MySQL VARCHAR Indexes Limited to 255 Characters in UTF8?

MySQL VARCHAR Index Length

Issue:

In MySQL, varchar columns with a prefix index greater than 255 characters are not being used to sort data, despite having enough index size.

Answer:

MySQL assumes 3 bytes per utf8 character in index calculations. With a 767-byte index limit, this translates to a maximum of 255 characters.

Explanation:

• MySQL index sizes are specified in bytes, even for non-binary data types like VARCHAR.
• The maximum prefix index size per column is 767 bytes.
• For utf8 columns, MySQL assumes each character occupies 3 bytes.
• Therefore, 767 / 3 = 255 characters is the maximum index length for utf8 columns.

Additional Considerations:

• Unique constraints on utf8 columns are limited to 255 characters due to the entire cell value being indexed.
• Regular indexes can be applied to columns larger than 255 characters, but will only index the first 255 characters.
• If most characters in the column are ASCII (1-byte characters), it's possible to fit more than 255 characters into the index, but MySQL's calculations do not account for this.
• With the innodb_large_prefix option enabled, the index limit can be increased to 3072 bytes for tables using the DYNAMIC or COMPRESSED row formats.

The above is the detailed content of Why Are MySQL VARCHAR Indexes Limited to 255 Characters in UTF8?. For more information, please follow other related articles on the PHP Chinese website!