get_limited_unindexed_count() very slow, here's a much faster SQL query, please implement it

[archon810]

• 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

This is a follow-up to #18065, which got closed and didn't get reopened, even after evidence that the issue is still present and a clear improvement query provided.

To Reproduce

Step-by-step reproduction instructions

1. See Yoast\W\S\A\I\Abstract_Indexing_Action->get_limited_unindexed_count() slow query #18065.

The shown queries take a very long time - multiple minutes. The suggested fixed queries are much faster - mere seconds. Implementing them will change the maddeningly slow experience opening Yoast pages to something that is fast at best and much more manageable at worst.

Screenshots, screen recording, code snippet

See #18065 (comment) and #18065 (comment).

Technical info

Used versions

• Device you are using: Windows Chrome
• Operating system: Linux
• PHP version: 8.1
• WordPress version: 6.7.1
• WordPress Theme: private
• Yoast SEO version: 23.9

[archon810]

Additionally, why does this query even run when accessing the site's Plugins wp-admin/plugins.php page? Every time I go to the Plugins page, it takes 2 minutes to load (For example, the most recent load: 121 queries | 99.907s).

Is it some filter for the text under the Yoast SEO (Activate your subscription | Required by Yoast SEO Premium | Finish your first-time configuration | FAQ | Settings)? Why is it hooking into Plugins at all?

SELECT COUNT(P.ID)
			FROM wp_posts AS P
			WHERE P.post_type IN ('post', 'page', 'attachment', 'apps_post', 'apk_split', 'app_release')
			AND P.post_status NOT IN ('auto-draft')
			AND P.ID not in (
				SELECT I.object_id from wp_yoast_indexable as I
				WHERE I.object_type = 'post'
				AND I.version = 2 )

[josevarghese]

Hi @archon810

Thanks for creating the issue. I recommend you answer the last comment from our team asking for the hosting setup at the previously opened issue. Once you reply with those details, we can re-open the issue and ask our development team to take a closer look.

I am closing this one to keep the conversation about the same issue on a single GitHub issue.