[wp-trac] [WordPress Trac] #50161: Slow query in WP_Posts_List_Table, no way to avoid with filters/hooks

[WordPress Trac]

#50161: Slow query in WP_Posts_List_Table, no way to avoid with filters/hooks
-------------------------------+------------------------------------------
 Reporter:  LucasMS            |       Owner:  (none)
     Type:  enhancement        |      Status:  reopened
 Priority:  normal             |   Milestone:
Component:  Posts, Post Types  |     Version:  5.4.1
 Severity:  normal             |  Resolution:
 Keywords:  has-patch          |     Focuses:  administration, performance
-------------------------------+------------------------------------------

Comment (by siliconforks):

 I'm skeptical how useful this index will actually be in practice - it
 seems like it is designed for just this one specific SQL query, and even
 for that query the index does not appear to entirely solve the issue.
 Even if it reduces the query time to 0.1 seconds, that is still a
 relatively slow query.  The problem is that database indexes are designed
 for retrieving specific data, not for counting things.  An index might
 make counting somewhat faster (because the database might be able to count
 using only the index and avoid reading the full table) but it is still
 likely to get slower and slower as more and more blog posts are added to
 the table.

 For what it's worth, I tested this on a Ubuntu 22.04 machine with MySQL
 8.0.42-0ubuntu0.22.04.2 and found that on this system MySQL does not
 actually use the index at all:
 {{{
 mysql> EXPLAIN SELECT COUNT( 1 )
     -> FROM wp_posts
     -> WHERE post_type = 'post'
     -> AND post_status NOT IN ( 'trash','auto-draft','inherit','request-
 pending','request-confirmed','request-failed','request-completed' )
     -> AND post_author = 1
     -> \G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: wp_posts
    partitions: NULL
          type: ref
 possible_keys: type_status_date,post_author,type_status_author
           key: post_author
       key_len: 8
           ref: const
          rows: 168194
      filtered: 50.03
         Extra: Using where
 }}}
 Granted, that might just be some bug in older MySQL versions and may not
 actually be an issue going forward.  Still, I don't think using an index
 is really a reliable way of speeding up counting things.  If you really
 want to solve the issue, I think you would have to do one of the
 following:

 1. Store the count somewhere instead of re-counting rows every time.
 (Note that WordPress already does something similar in a few places -
 e.g., `wp_posts.comment_count`, `wp_term_taxonomy.count`.)

 2. Do what @LucasMS originally suggested and provide a filter/hook to
 avoid counting rows entirely.  Note that it would not be necessary to
 remove the "Mine" link entirely - merely skipping the count would be
 sufficient.  (The other counts are not fast either, so it would be good to
 be able to skip those as well.)

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/50161#comment:11>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform