Improve `wp_count_posts()` query performance for users without `read_private_posts` capabilities

[rcorrales]

This ticket separates the work needed for the filter creation proposed in #61097 and focuses here on the performance optimization required.

Context:

Queries generated by the wp_count_posts() function for users without the read_private_posts capability incorporate the following conditions:

<?php
if ( ! current_user_can( $post_type_object->cap->read_private_posts ) ) {
        $query .= $wpdb->prepare(
                " AND (post_status != 'private' OR ( post_author = %d AND post_status = 'private' ))",
                get_current_user_id()
        );
}

This doesn't efficiently use indexes and makes the query extremely slow if there are millions of records in the wp_posts table.

Proposal:

Split the query into two simpler ones and join the results using UNION ALL so it makes better use of the existing indexes:

SELECT post_status, COUNT(*) AS num_posts
FROM (
    SELECT post_status
    FROM wp_posts
    WHERE post_type = %s AND post_status != 'private'
    UNION ALL
    SELECT post_status
    FROM wp_posts
    WHERE post_type = %s AND post_status = 'private' AND post_author = %d
) AS filtered_posts
GROUP BY post_status;

I tested this on a table with +14M records, and query time went down from 8 minutes to 11 seconds, generating the same results.

To discuss:

I haven't seen any examples of UNION ALL operators in core, even though MySQL has supported them for a long time (since the early 2000s). I'm unsure if it's by design or if there simply hasn't been a need for that.

If using UNION ALL is not permitted, then splitting the query into two get_results() calls and merging the results back in the app layer might also be an alternative, although a less performant one because of the network and code overhead. But it's possibly still better than executing the original query.

[rcorrales]

@snehapatil02 submitted this PR to address this performance issue:
​https://github.com/WordPress/wordpress-develop/pull/6774