Changeset 60788 for trunk/src/wp-includes/post.php

Timestamp:

09/21/2025 05:25:49 AM (12 months ago)

Author:

westonruter

Message:

Posts, Post Types: Improve wp_count_posts() query performance for users who cannot read_private_posts.

The query is refactored to use two subqueries which can leverage DB indexes.

Props rcorrales, snehapatil02, sirlouen, sajjad67, pbearne, johnbillion, westonruter.
Fixes #61097.

File:

• trunk/src/wp-includes/post.php (modified) (1 diff)

trunk/src/wp-includes/post.php

@@ -3401 +3401 @@
         }
 
-        $query = "SELECT post_status, COUNT( * ) AS num_posts FROM {$wpdb->posts} WHERE post_type = %s";
-
-        if ( 'readable' === $perm && is_user_logged_in() ) {
-                $post_type_object = get_post_type_object( $type );
-                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()
-                        );
-                }
-        }
-
-        $query .= ' GROUP BY post_status';
-
-        $results = (array) $wpdb->get_results( $wpdb->prepare( $query, $type ), ARRAY_A );
+        if (
+                'readable' === $perm &&
+                is_user_logged_in() &&
+                ! current_user_can( get_post_type_object( $type )->cap->read_private_posts )
+        ) {
+                // Optimized query uses subqueries which can leverage DB indexes for better performance. See #61097.
+                $query = $wpdb->prepare(
+                        "
+                        SELECT post_status, COUNT(*) AS num_posts
+                        FROM (
+                                SELECT post_status
+                                FROM {$wpdb->posts}
+                                WHERE post_type = %s AND post_status != 'private'
+                                UNION ALL
+                                SELECT post_status
+                                FROM {$wpdb->posts}
+                                WHERE post_type = %s AND post_status = 'private' AND post_author = %d
+                        ) AS filtered_posts
+                        ",
+                        $type,
+                        $type,
+                        get_current_user_id()
+                );
+        } else {
+                $query = $wpdb->prepare(
+                        "
+                        SELECT post_status, COUNT(*) AS num_posts
+                        FROM {$wpdb->posts}
+                        WHERE post_type = %s
+                        ",
+                        $type
+                );
+        }
+
+        $query  .= ' GROUP BY post_status';
+        $results = (array) $wpdb->get_results( $query, ARRAY_A );
         $counts  = array_fill_keys( get_post_stati(), 0 );