get_limited_unindexed_count impacts performance

[dgwatkins]

• 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

To Reproduce

Step-by-step reproduction instructions

1. Yoast plugin is checking every 15 minutes (based on transient set) if there are some posts that are not indexed
2. Query to fetch those takes around 20 seconds to return the data
3. Query runs on any page in admin since it's hooked to admin_init
4. Query returns about 5273840 results on our production site (expected due to large dataset)
5. If we try to write the same query, but using JOIN instead of subselect, we can get drastic improvement in time
6. This seems to be a duplicate of get_limited_unindexed_count() very slow, here's a much faster SQL query, please implement it #21854 but I am reporting again because it was already closed

Expected results

1. To not have an expensive query running every 15m

Actual results

1. Significant slow down each time the transient expires

Screenshots, screen recording, code snippet

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

Technical info

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

• Block Editor
• Gutenberg Editor
• Elementor Editor
• Classic Editor
• Other:

• Which browser is affected (or browsers):

• Chrome
• Firefox
• Safari
• Other:

Used versions

• Device you are using: Chrome on laptop
• Operating system: MacOs
• PHP version: 7.4
• WordPress version: 6.7.1
• WordPress Theme: Custom
• Yoast SEO version: latest
• Gutenberg plugin version:
• Elementor plugin version:
• Classic Editor plugin version:
• Relevant plugins in case of a bug:

[josevarghese]

Hi @dgwatkins

Thank you for taking the time to report this issue in detail. It was previously reported here: (#18065), but it was closed due to a lack of interaction.

I have internally shared the issue you’ve raised to request the re-opening of the above-mentioned issue.

[josevarghese]

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