Why Isn\'t My MySQL VARCHAR(512) Index Being Used?

MySQL VARCHAR Index Length

MySQL tables have a maximum index length of 767 bytes. This is per column, so a composite index cannot exceed 767 bytes in total. However, there are some exceptions to this rule:

• With the innodb_large_prefix configuration option enabled, the index length limit is raised to 3072 bytes for InnoDB tables using the DYNAMIC or COMPRESSED row formats.
• For utf8 character sets, MySQL assumes 3 bytes per character. Therefore, the maximum number of characters that can be indexed is 255 (767 / 3).

In your specific case:

• Your products table has a name column of type varchar(512).
• You created an index on this column with ALTER TABLE products ADD INDEX (name(512)).

The index you created is not being used because it exceeds the maximum index length of 767 bytes. This is because MySQL assumes 3 bytes per UTF-8 character, and 512 characters * 3 bytes/character = 1536 bytes.

To resolve this issue, you can:

• Reduce the length of the index to 255 characters or less.

  ALTER TABLE products ADD INDEX (name(255));

• Enable the innodb_large_prefix configuration option and use the DYNAMIC or COMPRESSED row format.

  SET innodb_large_prefix = ON;
  
  ALTER TABLE products ROW_FORMAT=COMPRESSED;

Additional Notes:

• If you are storing mostly ASCII characters (1 byte per character), you can specify an index of up to 767 bytes.
• It is possible to store 4-byte characters using the utf8mb4 character set. However, this will further reduce the maximum number of characters that can be indexed.

The above is the detailed content of Why Isn\'t My MySQL VARCHAR(512) Index Being Used?. For more information, please follow other related articles on the PHP Chinese website!