Change all meta_key columns to VARCAR(191)

There is an issue with the meta_key field in all meta tables. This issue is that the fields in all meta tables (_postmeta, _usermeta, _commentmeta, _term_meta) are all set as VARCHAR(255). These should be VARCHAR(191)

The meta_key fields in all of these tables is indexed. However, the maximum length of the key is 191 characters. Actually the byte limit is 767 and in utf8mb4 that give us the 191 limit.

What this means is that when searching for a meta_key value the index may not be used used because the field can be longer than the index prefix. I’m going to be honest I don’t know all of the technical details here. The only thing that I can say is that I have tested installations where I have altered these DB tables to set the meta_key fields to VARCAR(191) and this has resulted in slightly faster queries. Considering the number of queries made to these meta tables or when doing a "meta_query on posts, this increase in speed can be significant

This issue was address for the options table when the option name field was increased from 64 characters and it was discussed there why 191 was a better choice than 255. You can read that entire discussion there. You can read the entire discussion here if you’d like https://core.trac.wordpress.org/ticket/13310.


Read-only archive: https://petitions.classicpress.net/posts/74/change-all-meta_key-columns-to-varcar-191

Author: John A. Huebner II

Vote count: 20

Status: open

Tags:

  • difficulty-moderate
  • request-modify-feature

Comments