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

Timestamp:

09/21/2025 05:25:49 AM (8 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 );