Slow WooCommerce backend? Try this.

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 wp_postmeta 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.

SELECT meta_key, LENGTH(meta_key) FROM wp_postmeta ORDER BY LENGTH(meta_key) DESC LIMIT 5;

Leave a Reply

Your email address will not be published. Required fields are marked *