Filter `wp_count_posts()` query before execution to avoid slow query

[rcorrales]

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.

One way to fix this could be to split the query:

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;

In my tests with a table with +14M records, query time went from 8 minutes to 11 seconds, generating the same results. But 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 modifying the query like that is not possible, could we add a pre_wp_count_posts filter before executing it so it can be overridden?

[matteoenna]

I'll start working on it

[johnbillion]

@rcorrales Thank you for the ticket!

This was briefly discussed during the performance team chat today and we agreed to add a filter for this query. Would you like to open a separate ticket for discussing the improvement to the query itself? That will need further discussion, unit test coverage, and performance reports. Thanks!

## Ticket
https://core.trac.wordpress.org/ticket/61097

## Description

• This PR proposes optimization to the wp_count_posts() function to improve query performance, especially for users without the read_private_posts capability.
• The current implementation of the function's query includes inefficient conditions, leading to slow queries, particularly in databases with millions of records.
• The proposed solution involves replacing the existing query logic with an optimized UNION ALL query, which separates the selection of non-private posts and private posts authored by the current user.
• Additionally, the PR ensures backward compatibility by maintaining the original logic when the conditions for optimization are not met.

## Changes Made

• Replaced the existing query logic in the wp_count_posts() function with an optimized UNION ALL query.
• Incorporated conditional logic to use the optimized query only when necessary, falling back to the original logic otherwise.

## Context
The wp_count_posts() function is critical for counting posts of a specific type in WordPress. However, the current implementation suffers from performance issues due to inefficient query conditions. By optimizing the query with a UNION ALL approach, this PR aims to significantly improve performance, especially for large datasets.

Referencing this PR on a new ticket focused on the performance of this query:
https://core.trac.wordpress.org/ticket/61502

[rcorrales]

@johnbillion I went ahead and created a separate ticket (#61502) for focusing on the performance improvements, while we add a new filter for this query using this ticket.

@snehapatil02 Thanks for submitting the PR! I referenced it in a comment in the new ticket.

It seems like it can't be added to the "Pull Requests" section of the new ticket unless it's mentioned in the PR description.

@pbearne Fixed the phpcs errors.

@pbearne Do I need to make any more changes??