[wp-trac] [WordPress Trac] #61502: Improve `wp_count_posts()` query performance for users without `read_private_posts` capabilities

[WordPress Trac]

#61502: Improve `wp_count_posts()` query performance for users without
`read_private_posts` capabilities
-------------------------------+-----------------------------
 Reporter:  rcorrales          |      Owner:  (none)
     Type:  enhancement        |     Status:  new
 Priority:  normal             |  Milestone:  Awaiting Review
Component:  Posts, Post Types  |    Version:
 Severity:  normal             |   Keywords:
  Focuses:  performance        |
-------------------------------+-----------------------------
 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
 <?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:

 {{{#!php
 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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/61502>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform