Performance of get_total_unindexed Query

[travisanderson99]

• I've read and understood the contribution guidelines.
• I've searched for any related issues and avoided creating a duplicate issue.

Please give us a description of what happened

When visiting /wp-admin on our site, this query is taking roughly 12 seconds to execute on our sites:

Yoast\WP\SEO\Actions\Indexing\Abstract_Indexing_Action->get_total_unindexed

SELECT COUNT(P.ID)
FROM wp_2_posts AS P
LEFT JOIN wp_2_yoast_indexable AS I
ON P.ID = I.object_id
AND I.link_count IS NOT NULL
AND I.object_type = 'post'
LEFT JOIN wp_2_yoast_seo_links AS L
ON L.post_id = P.ID
AND L.target_indexable_id IS NULL
AND L.type = 'internal'
AND L.target_post_id IS NOT NULL
AND L.target_post_id != 0
WHERE ( I.object_id IS NULL
OR L.post_id IS NOT NULL )
AND P.post_status = 'publish'
AND P.post_type IN ('post', 'page', 'players', 'archived-players', 'colleges', 'products', 'graphics', 'partners', 'programs', 'blogs', 'high_schools', 'games', 'auto_articles')

We do have a very large wp_2_posts table (slightly over 1 million entries) which is likely a factor in the query time.

To Reproduce

Step-by-step reproduction instructions

1. Visit /wp-admin on a site with Yoast installed with a very large wp_posts table, ideally hundreds of thousands of posts.
2. Investigate the response time for the following query:

Yoast\WP\SEO\Actions\Indexing\Abstract_Indexing_Action->get_total_unindexed

Screenshots, screen recording, code snippet

If possible, please provide a screenshot, a screen recording or a code snippet which demonstrates the bug.

A screenshot from the Query Monitor plugin:

Technical info

• If relevant, which editor is affected (or editors):

• Block Editor
• Gutenberg Editor
• Elementor Editor
• Classic Editor
• [ x] Other: Main admin dashboard

• Which browser is affected (or browsers):

• [x ] Chrome
• [x ] Firefox
• [x ] Safari
• Other:

Used versions

• Device you are using:

• Operating system:

• PHP version: 8.2

• WordPress version: Latest (6.7)

• WordPress Theme: Custom

• Yoast SEO version: 23.7

• Relevant plugins in case of a bug:

[josevarghese]

Hi @travisanderson99

Thanks for using the Yoast SEO plugin and for reporting this issue.

While the query is aggressively cached to minimize its occurrence, it may take longer to process on slower sites. If your SEO optimization is complete (Yoast SEO > Tools), you might consider adding the following filter to your site:

add_filter( 'Yoast\WP\SEO\enable_cron_indexing', '__return_false' );

This will reduce the frequency of this query running, especially on the initial wp-admin page load, which might be the main source of the issue.

In case the SEO Data Optimization process is not yet completed on your website (Yoast SEO > Tools), we recommend you update the Yoast SEO plugin to 24.0 and run it before to check whether any improvements happen.

We hope this helps! Feel free to let us know if you need any more help.

[travisanderson99]

Thanks for the reply, Jose. I ran the optimization tool but I'm still that Yoast query being flagged as slow unfortunately:

Yoast\WP\SEO\Actions\Indexing\Abstract_Indexing_Action->get_total_unindexed | 8.9 seconds

If I were to add this filter to our sites, would that prevent indexing entirely or just reduce the frequency? In either case, would that cause any issues with our SEO?

add_filter( 'Yoast\WP\SEO\enable_cron_indexing', '__return_false' );  

I would like to prevent this query from running if it's not essential but I also don't want to negatively impact SEO as a result.

[josevarghese]

Hi @travisanderson99

Thanks for getting back to us.

Quick answer: Within the filter, the SEO Data Optimization process via CRON is only getting disabled. So it won't affect your SEO.

As you have already completed the SEO Data Optimization process, running the query within the Dashboard to run the SEO Data Optimization via cron will only be disabled. The posts or pages that are set via our settings page will be shown in the source code as such without any changes, and we can confirm that this filter won't affect your SEO.

[travisanderson99]

Sounds good, I will give that filter a shot. Thanks for your help, Jose!