Performance of author_has_public_posts_wp

[joshuatf]

The check for author_has_public_posts_wp performs a potentially slow query on larger sites. Related to #20110.

Is your feature request related to a problem? Please describe.

On a site with ~1.6 million posts, the query to check for public posts is slow. This query can occur whenever an index is not present and any list page is loaded.

In this site, the query took almost 3s (even in a dedicated local environment).

SELECT wp_posts.ID
FROM wp_posts
WHERE 1=1
AND wp_posts.post_author IN (26062)
AND wp_posts.post_type = 'post'
AND ((wp_posts.post_status = 'publish'))
ORDER BY wp_posts.post_date DESC
LIMIT 0, 1

Describe the solution you'd like

I'm not sure if the ORDER BY is necessary in the above query if we're just checking for the existence of a public post. If not, we can remove that from the query.

Additionally, I wanted to double check that this query is run on CRON when enable_cron_indexing is enabled.

Why do you think this feature is something we should consider for the Yoast SEO plugins?

This query significantly impacts load times on larger sites and can easily be optimized.

Additional context

Discovered during the WooCommerce performance spike.

[josevarghese]

Hi @joshuatf

Thank you for creating this issue and bringing it to our attention. We truly value feedback from our users, as it helps us identify areas for improvement.

I have escalated this issue internally to our development team for further investigation. They will review it thoroughly to ensure we address the concern effectively.

We’ll keep you updated on the progress and resolution. In the meantime, please don’t hesitate to share any additional details or suggestions you might have.

[josevarghese]

Internal Slack conversation: https://yoast.slack.com/archives/C01NCRHHN30/p1737620647841019