Make WordPress Core

Opened 7 weeks ago

Last modified 7 days ago

#61097 new enhancement

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

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

Description

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?

Change History (5)

This ticket was mentioned in Slack in #core-performance by mukeshpanchal27. View the logs.


6 weeks ago

#2 @mukesh27
6 weeks ago

  • Keywords needs-patch dev-feedback added
  • Version 6.5 deleted

#3 @matteoenna
6 weeks ago

  • Keywords needs-patch dev-feedback removed
  • Version set to 6.5

I'll start working on it

#4 @johnbillion
6 weeks ago

  • Component changed from Query to Posts, Post Types
  • Keywords needs-patch added

@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!

This ticket was mentioned in PR #6774 on WordPress/wordpress-develop by @snehapatil02.


7 days ago
#5

  • Keywords has-patch added; needs-patch removed

## 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.

Note: See TracTickets for help on using tickets.