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.

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

1 Answer 1

Reset to default
5

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

Improve this answer
3
  • 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
    Commented Jun 28, 2016 at 3:23
  • This was from a few years ago? Is there any update to this with MariaDB 10.4? If I need to stay with UTF8mb4 how can I do a varchar(255)?
    – Matt Winer
    Commented Apr 23, 2020 at 22:59
  • @MattWiner - Thanks for the question. Setting Baraccuda and large_prefix have gone away. Some updates are here: mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes I think the straight answer to your comment is "Yes". But I have not double checked.
    – Rick James
    Commented Apr 25, 2020 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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