Home > Database > Mysql Tutorial > Why Isn\'t My MySQL VARCHAR(512) Index Being Used?

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

Susan Sarandon
Release: 2024-11-25 03:56:12
Original
437 people have browsed it

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));
    Copy after login
  • 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;
    Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template