Mysql – WordPress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci

indexMySQLvarcharWordpress

I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current WordPress installation, I'm finding varchar fields used for indexes which are longer than the 191 characters, which the documentation seems to say is the max. I'm running MariaDB 10.x which I believe matches up with MySQL 5.7, and the documentation confirms the limit when using utf8mb4_unicode_ci collation and compact as the format.

I'm finding varchar(200) and varchar(255) indexed fields, e.g. wp_postmeta, the field meta_key is varchar(255). Am I wrong or confused on this? The db is using the InnoDB engine. Thanks for any clarification.

Best Answer

(This answer is probably more than you wanted. It is an in-depth discussion of the source of the problem, plus multiple solutions.)

Long ago, when MySQL added CHARACTER SET utf8, it needed up to 3 bytes per character. And indexing was limited to 767 bytes, enough for VARCHAR(255).

When utf8mb4 was added (because utf8 was incomplete), the encoding needed up to 4 bytes/char, but the index limit was not increased. (Oops.) So, indexing became limited (for utf8mb4) to 191 characters ((767-2)/4).

That problem lasted throughout 5.5 and 5.6 (MariaDB 5.5 - 10.1?). 5.7 cleaned things up.

There are many solutions to this issue:

  • Change back to utf8. Most users can get away from this. But, if you need Emoji or all of Chinese, you need utf8mb4.
  • Limit your indexed VARCHARs to 191 instead of 255. This can usually be done safely, but you should check the data, and understand that it limits the strings that you can use.
  • (see jkavalik's comment) Use "prefix" indexing: INDEX (col(191)). This has two negative effects: (1) If the index is really UNIQUE, you get the wrong interpretation of the uniqueness constraint. (2) Performance probably suffers because of the inefficiencies of prefixing.
  • In 5.5.14, 5.6.3, 5.7.7, MariaDB 5.5, the following combination raises the limit from 767 to 3072: innodb_large_prefix=1 (defaulted on in 5.7.7), innodb_file_format=Barracuda, innodb_file_per_table=1, ROW_FORMAT = DYNAMIC or COMPRESSED.

It would be nice if WordPress proactively tackled the problem you encountered.

(Update) In later versions, these are deprecated (5.7 and 10.2?) because they are no longer necessary: innodb_file_format, innodb_file_format_check, innodb_file_format_max and innodb_large_prefix. If you get an error to that effect, simply don't bother setting them. They are actually removed in 8.0 / 10.4(?)

Related Question