Wordpress-seo: Slow post meta query

[spacedmonkey]

Please give us a description of what happened.

While looking at yoast panels in the CMS, there it generates a slow media query on the database.

I am using Yoast 11.1.1 and WordPress 5.2

Technical info

The query is fired on this url wp-admin/admin.php?page=wpseo_titles

SELECT DISTINCT meta_key
            FROM wp_postmeta
            WHERE meta_key NOT BETWEEN ? AND ?
            HAVING meta_key NOT LIKE ?
            ORDER BY meta_key
            LIMIT ? ? 

and can in the following stack trace.

in mysqli_query called at /var/www/wp-content/db.php (1186)
in hyperdb::ex_mysql_query called at /var/www/wp-content/db.php (827)
in hyperdb::query called at /var/www/wp-includes/wp-db.php (2454)
…www/wp-content/plugins/wordpress-seo-premium/inc/
class-wpseo-custom-fields.php (47)
…ugins/wordpress-seo-premium/admin/
class-admin-editor-specific-replace-vars.php (42)
…ed at /var/www/wp-content/plugins/wordpress-seo-premium/admin/
class-config.php (122)
…ed at /var/www/wp-content/plugins/wordpress-seo-premium/admin/
class-config.php (72)
…in_Pages::config_page_scripts called at /var/www/wp-includes/
class-wp-hook.php (286)
in WP_Hook::apply_filters called at /var/www/wp-includes/class-wp-hook.php (310)
in WP_Hook::do_action called at /var/www/wp-includes/plugin.php (453)
in do_action called at /var/www/wp-admin/admin-header.php (97)
in require_once called at /var/www/wp-admin/admin.php (216)

According to query monitor, the query took 0.8990 seconds. Which is pretty slow. This query would only get waste with the more rows in the post meta table. Those running sites with 100k+ posts would like, having serious issues loading this page.

In WordPress 5.1, the ability to do LIKE search by meta key, which will at least mean that those running query caching with enhanced-post-cache or advanced-post-cache would get a cached result.

Related: https://github.com/Yoast/wordpress-seo/issues/12005

[spacedmonkey]

Looking into this query, wouldn't it be better to just get all meta_key in post meta table and do the filter in PHP by looping through?

[Djennez]

Thanks for the details and suggestions. I was able to reproduce this issue with 100000 random entries in my wp_postmeta table, raising the load time to 0,86 seconds for this query alone.

I'll pass this to our product team to see if, what and how this can be improved.

[SimonJThompson]

Just to add to this; we have a site with ~6.5M postmeta rows, and this is raising the query time to ~14s on /wp-admin/admin.php?page=wpseo_titles.

If there's anything we can do to help with testing etc. to get this into an upcoming release, please let us know!

[welford-media]

Reporting same issue. Query is:

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

Any update for this issue?

[teknofilo]

Also quite slow here, around 4 seconds

[markdwhite]

This is a core WP bug that has been around for some time. The current ticket is here:

https://core.trac.wordpress.org/ticket/33885

If you're really struggling with this, the link in comment #78 points here, which offers a function to remove loading of the custom fields box entirely:

https://9seeds.com/wordpress-admin-post-editor-performance/

In the past I've used a similar approach to hardcode the results returned by the query into a function, but can't find it right now. This might work if you need custom fields, but there a small static number of them which you actually use.

[archon810]

We have 11mln rows in wp_postmeta and this query is taking 40 seconds. That's an absolutely insane slowdown. It needs to go or be reworked and cached because it isn't using an index.

Just look at this EXPLAIN:

1   SIMPLE  wp_postmeta range   meta_key,meta_key_value_id  meta_key    767     5569246 Using where; Using temporary; Using filesort

This is really really really bad.

[ocean90]

Related: https://developer.yoast.com/upcoming-release-yoast-seo-14-0-indexables/

[archon810]

Oh boy, now I'm truly scared.

[herregroen]

This won't be magically fixed with the major feature of 14.0, indexables. However I did take a look and made some optimizations.

Removing the ORDER BY removes the filesort which should primarily increase performance, given these variables are processed by our JS the order doesn't matter. I've also replace the NOT LIKE '%_' with a SUBSTRING call which EXPLAIN seems to like better as it's giving me higher filtered percentages, do take into account that those are only an estimation.

The fix is currently scheduled to be part of 14.0 as well.

[archon810]

@herregroen Great news. Can you please post the full query here so I can analyze its performance on our end?

[Djennez]

This is the PR in its current state: https://github.com/Yoast/wordpress-seo/pull/14781

[herregroen]

@archon810 The query, with it's default limit would be as follows:

SELECT DISTINCT meta_key
FROM wp_postmeta
WHERE meta_key BETWEEN '_' AND '_z' AND SUBSTRING(meta_key, 1, 1) != '_'
LIMIT 30

It should have increased performance over the previous iteration however the root cause here is that the wp_postmeta table is not indexed correctly which is, unfortunately, not something we can fix as a plugin.

In order to fix that you'd have to ensure the meta_key column and meta_key index have the same length. By default in WordPress the meta key column has a length of 255 while the index has a length of 191 ( which is the maximum in MySQL without the innodb_large_prefix setting being enabled ). This means that the meta_key index is essentially never used.

Enabling the innodb_large_prefix setting and changing the index length to 255 would ensure the index is used and would speed up these queries far more than any change we could ever do.

Please do ensure you have a proper staging or test environment to test such changes on and you have made back-ups of your database beforehand.

[archon810]

@herregroen I can confirm that the new query is taking only 4-5s vs 57s currently.

However, the new query returns 0 results, whereas the old one returns 30 for us (which is the LIMIT in the query). Looking at the query you posted, it seems to be quite different from the original. Did you mean NOT BETWEEN?

Update: Using NOT BETWEEN instead of BETWEEN in the new query produces the same data set as the original query, but this time in 14s. So the right query, it seems, is:

SELECT SQL_NO_CACHE DISTINCT meta_key
FROM wp_postmeta
WHERE meta_key NOT BETWEEN '_' AND '_z' AND SUBSTRING(meta_key, 1, 1) != '_'
LIMIT 30    

It's ~3 times faster because it no longer, which is decent, but still quite slow.

1   SIMPLE  wp_postmeta range   meta_key,meta_key_value_id  meta_key    767     5516621 Using where; Using temporary

[archon810]

Btw, the query only gets to keys starting with letter d for us. 30 is not nearly enough to enumerate all of them.

[archon810]

Holy smokes, you weren't kidding about the magic of matching the index length to the column length. I always thought for most cases, including this query, the sub-index on 191 chars is enough and 255 matching one wouldn't make a huge difference, but boy was I wrong.

1   SIMPLE  wp_postmeta range   meta_key_value_id,meta_key  meta_key    1023        161 Using where; Using index for group-by; Using temporary

Both queries are now instant: 0.019s (I verified with SQL_NO_CACHE).

[herregroen]

@archon810 Good to hear that helped. Sorry about the missing NOT, that was an unfortunate typo on my part.

The limit of 30 is there to avoid the query taking even more time on large sites that haven't fixed their index length and to avoid situation on sites that have very large amounts of private meta keys that aren't prefixed with _.

[herregroen]

This has been fixed in #14781

[Uranbold]

Are you guys sure about this issue has fixed? I have WP 5.4.1 Yoast SEO 14.2 posts 700K+ huge slow on admin because of this query!

[Uranbold]

How did you change the Length of the meta_key field on the database? If I understand correctly then it should disappear. Yoast index is loading 2 days almost but I have so many posts and it only passed 5% full 2-day indexing...

Screenshot of image

[kevin25]

Happened the same to me. Any fixes?

[reza465]

Hello dear programmers

My website is in trouble
Detected by the monitor query plugin

My problem is a little different
Please advise me what to do
I use free version 15 of yoast SEO

Support could not resolve the issue
I was transferred to this github

Please watch the short videos below

https://digit-shop.com/wp-content/uploads/2020/09/Rec.mp4 <<<< All plugins are up to date

https://digit-shop.com/wp-content/uploads/2020/09/Rec-1.mp4 <<<< Show bugs

https://digit-shop.com/wp-content/uploads/2020/10/Rec-2.mp4 <<<< Disable all plugins

https://digit-shop.com/wp-content/uploads/2020/10/Rec_3.mp4 <<<< Index plugin again yoast seo

https://digit-shop.com/wp-content/uploads/2020/10/Rec_4.mp4 <<<< The problem still persists

Thanks very much