[wp-trac] [WordPress Trac] #61097: Filter `wp_count_posts()` query before execution to avoid slow query

[WordPress Trac]

#61097: Filter `wp_count_posts()` query before execution to avoid slow query
-------------------------+-----------------------------
 Reporter:  rcorrales    |      Owner:  (none)
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Query        |    Version:  6.5
 Severity:  normal       |   Keywords:
  Focuses:  performance  |
-------------------------+-----------------------------
 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.

 One way to fix this could be to split the query:

 {{{#!sql
 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?

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