Why Does MySQL\'s varchar Index Length Limit Differ Between Versions?

MySQL varchar Index Length

While exploring an SQL query performance issue, the user attempted to optimize the query by adding an index to the products.name column, which is defined as varchar(512). The original index, specified as product_name_idx (name(512)), resulted in the index not being used for sorting, as it was identified as a prefix index.

To address this, the user tried truncating the name column to varchar(255). Following this change, the subsequent EXPLAIN statement indicated that an index was being used.

This behavior stems from the assumption MySQL makes regarding the length of UTF-8 characters. In MySQL, the maximum index size for a single column is 767 bytes. Prior to MySQL 8.0, the utf8 character set was limited to a maximum of 3 bytes per character. However, with MySQL 8.0, this has changed, and utf8mb3 is now deprecated and replaced by utf8mb4, which supports up to 4 bytes per character.

In MySQL versions prior to 8.0, specifying an index length greater than the default of 255 characters for a varchar(255) column would result in an error. This is because MySQL assumes 3 bytes per character for UTF-8, and a 256 character index would exceed the 767 byte limit.

With MySQL 8.0 and the utf8mb4 character set, the assumption of 3 bytes per character still applies. However, with the introduction of compressed and dynamic row formats, index key prefixes can be up to 3072 bytes. As a result, indexes can be created on varchar columns with a larger number of characters.

To summarize, in earlier MySQL versions, the index length limit for varchar columns was 255 characters due to the assumption of 3 bytes per UTF-8 character and the 767 byte maximum index size. With MySQL 8.0 and utf8mb4, this limit has been relaxed for compressed and dynamic row formats, allowing for longer indexes on varchar columns.

The above is the detailed content of Why Does MySQL\'s varchar Index Length Limit Differ Between Versions?. For more information, please follow other related articles on the PHP Chinese website!