3

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.

  • 1
    I think you're right. It's probably a leftover from when it used the stock UTF8 charset. – Synchro Jun 14 '16 at 6:35
  • As a workaround you can use prefix indexes: alter table T add index ind(col(191)); – jkavalik Jun 14 '16 at 19:22
2

(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).

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.

  • Thanks Rick, that's actually very helpful! I had pieced together the reason for the old settings but was surprised that WP would not have dealt with it by now. The site was a clean, new install. You have given me a new option, however, for getting around this and possibly a better format for my DBs - Barracuda. On MariaDB, the default format is Antelope so I'm looking into changing that. Thanks! – David Rahrer Jun 28 '16 at 3:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.