@av3nger We have created internally a task to assess this and added it on our board.

As per our processes, we will open a separate PR (that will link to this one) in order to tweak your solution and potentially merge that instead, but if we do we will give you props in our changelog.

I've also added a fix regarding using the new order of placeholders because otherwise the resulting queries would get malformed :)

@av3nger I have been investigating your PR and I'm actually not seeing any noticeable improvements when benchmarking both solutions 🤔 In most setups I actually see a tiny regression even, in terms of average time for the new query to complete, compared to the production one.

This is my methodology for benchmarking the queries btw:

• Add the following snippet in a mu-plugin:

add_filter( 'shutdown', 'benchmark_yoast_code', 10 );
function benchmark_yoast_code() {
    if ( ! get_option( 'benchmark_ran' ) ) {
        update_option( 'benchmark_ran', true );
        $indexation_action = YoastSEO()->classes->get( Yoast\WP\SEO\Actions\Indexing\Indexable_Post_Indexation_Action::class );
        // Define the number of times the function should be executed
        $numRuns = 100;
        
        // Start the benchmark
        $start = microtime(true);
        
        // Execute the function multiple times
        for ($i = 0; $i < $numRuns; $i++) {
            delete_transient( 'wpseo_total_unindexed_posts' );
            delete_transient( 'wpseo_total_unindexed_posts_limited' );
            $indexation_action->get_limited_unindexed_count(25);
        }
        
        // End the benchmark
        $end = microtime(true);
        
        // Calculate the elapsed time
        $elapsed = $end - $start;
        
        error_log( "Elapsed time: " . $elapsed . " seconds\n" );
        error_log( "Average time per run: " . ($elapsed / $numRuns) . " seconds\n" );
    }
}

• Every time you want to benchmark again, you should delete the benchmark_ran option from your db (I added that to avoid multiple runs)

If you try the same methodology and get opposite results, I'd be interested to look further on any potential differences our setups might have. (you do have to apply this fix on your PR as well first though 🙂 )

@leonidasmi, what is the select_type for the second query set to on your setup? Is it SUBQUERY or something else? If the subquery is being cached, then, you are right, the difference is not noticeable. However, if the second query is executed as a SUBQUERY then this could become a potential bottleneck, when we have a large number of posts, especially when the db cache hits limits. Also, won't the same query be cached on the SQL server, so running this 100 times will not really be helpful without flushing the caches on SQL?

@av3nger I'm getting this for the original query:

and that is on a website with ~48k posts. And when I compare it with this PR's query:

I'm noticing that the original query is slightly more performant in that site too. And that can be explained by the ref column, as the first query has a const there compared to the new query that is not completely constant.