We have a client that was having near 30 second page loads within the admin area of their WordPress website. Upon researching the issue, we narrowed it down to the query that appeared to be the culprit:
SELECT DISTINCT meta_key
FROM wp_postmeta
WHERE meta_key NOT BETWEEN '_'
AND '_z'
HAVING meta_key NOT LIKE '\\_%'
ORDER BY meta_key
LIMIT 30
We then performed some google magic and came upon a thread discussing the issue:
https://core.trac.wordpress.org/ticket/24498
Which then led to this one:
https://core.trac.wordpress.org/ticket/33885#comment:2
Basically, there is an index on the meta_key
column of the wp_postmeta
table, however, the meta_key
column needs to be resized to be a varchar(191)
field, rather than a varchar(255)
. After performing the following update, the performance skyrocketed. Page loads moved to being around 1 second.
ALTER TABLE wp_postmeta MODIFY meta_key varchar(191);
You can check the longest key found in your
table by running the following query. This will let you know if the above query would be suitable. On our client’s table, the max length in there was 49 characters, so I felt comfortable in making this change, being that it is nearly 4x the length of our longest key.wp_postmeta
SELECT meta_key, LENGTH(meta_key) FROM wp_postmeta ORDER BY LENGTH(meta_key) DESC LIMIT 5;