Make WordPress Core

Opened 4 weeks ago

Last modified 4 weeks ago

#61502 new enhancement

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

Reported by: rcorrales's profile rcorrales Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Posts, Post Types Keywords:
Focuses: performance Cc:

Description

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.

Change History (1)

#1 @rcorrales
4 weeks ago

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

Note: See TracTickets for help on using tickets.