What’s up with WordPress’s prefix keys like (meta_key(191))? A prefix key is a database key on part of a database column. For example, the wp_postmeta table has a column known as meta_key containing up to 250 characters. WordPress’s standard key for that table is a prefix key on the first 191 characters of that column. Why did WordPress’s developers choose that particular prime number as the length of the prefix? Good question. The answer lies in the history of WordPress and MySQL.
MySQL’s original data access method, known as MyISAM, imposed a built-in length limit of 767 bytes on the index for each column. That limit carried over into the InnoDB storage engine’s older Antelope data format. Barracuda, the newer data format, increased the limit to 3072 bytes. Bytes, not characters.
Older versions of WordPress, prior to version 4.2, stored our information with a Unicode-based multinational character set known as utf8. utf8 indexes take three bytes to store each character, so our 250-character meta_key column’s index used 750 bytes. That fits nicely within the 767-byte limit.
In 2015 the MySQL and WordPress user communities discovered that storing data with utf8 characters was not good enough. For example, there’s no way in utf8 to represent emoji characters and some other national-language characters. Both MySQL and WordPress are committed to serving everybody in the world. So MySQL added the utf8mb4 character set to allow it to store all Unicode characters. But, utf8mb4 characters need four, not three, bytes in the index for each character. No longer does the index for our 250-character meta_key column fit in 767 bytes. But 191 characters do fit. Hence the 191-character prefix index.
WordPress upgraded all our databases from utf8 to utf8mb4 (excluding some users of antique versions of MySQL) with their version 4.2. It went live on April 23, 2015. At that time they converted our tables’ indexes to prefix indexes. Most WordPress site owners didn’t notice the conversion: the 4.2 upgrade process said “upgrading database” and we ignored it. After 4.2 we all have prefix indexes on our data. The WordPress core team chose not to shorten the columns themselves to 191 bytes in the upgrade. No doubt they concluded that shortening peoples’ data would damage enough sites that it wasn’t worth the trouble. The prefix indexes didn’t damage data, only large-site performance.
Over the next few years, MySQL and MariaDB rolled out InnoDB’s Barracuda format. Its 3072-byte index length-limit was enough to handle 250-character columns like meta_key without prefixes. That’s where this plugin comes in. Because the 767-byte limit is gone, we can change most prefix indexes back to full-column indexes, and we can use compound (multi-column) indexes like PRIMARY KEY (post_id, meta_key, meta_id) effectively.
WordPress’s tables include many columns, like meta_value in wp_postmeta, that use the LONGTEXT data type. Those columns can hold up to 4 billion (4,294,967,295) characters. (That’s a large number. Not even this long WordPress page uses that many!) Indexes on those columns necessarily must be prefix indexes. Where we must use prefix indexes in this plugin we use a 32-character prefix. That makes for smaller and faster indexes, and still serves to speed up lookups.
If your version of MariaDB or MySQL still imposes the 767-byte limit — because it supports Antelope and not Barracuda — the plugin pesters you to upgrade with this notice.
Why did the WordPress team choose the prime number 191? Not because it’s prime. It is simply the value of ( 767 / 4 ) rounded down. It’s the longest utfmb4 prefix that fits in the 767-byte limit.