(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
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
- 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),
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_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(?)
alter table T add index ind(col(191));