[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:                     |     Focuses:  administration, performance
-------------------------------+------------------------------------------

Comment (by josephscott):

 To fill out the details on the potential impact of a new index to speed
 things the `All Posts` page up I setup a fresh WP 6.8.1 install with PHP
 8.4.7, MySQL 8.4.2 on my Apple M3 laptop ( no memcached or redis ).

 I'm specifically looking at how long it takes for the initial page request
 to finish for `/wp-admin/edit.php`.  I generated 340,000 posts for this
 test.  They are spread across two users, and the admin user view of the
 `All Posts` page shows:

 - All: 340,000
 - Mine: 339,999
 - Published: 339,800
 - Drafts: 100
 - Private: 100

 Again, looking only at how long the initial page request for `/wp-
 admin/edit.php` was taking, I was seeing times in the 0.820 seconds range.
 This wasn't TTFB, it was the total time to get the single HTML page
 response back.  After enabling SPX to get an idea of which parts were
 taking the longest, the vast majority of the time was spent in
 `wpdb::_do_query`, which wasn't a huge surprise.

 But looking at just the `_do_query` parts from the SPX report showed that
 one query in particular was the slowest part.  It was the query from
 class-wp-posts-list-table.php -
 https://github.com/WordPress/WordPress/blob/2f41f3fdaded86e2f4baca87001138542fb7fc55
 /wp-admin/includes/class-wp-posts-list-table.php#L92-L102

 {{{
                 $this->user_posts_count = (int) $wpdb->get_var(
                         $wpdb->prepare(
                                 "SELECT COUNT( 1 )
                                 FROM $wpdb->posts
                                 WHERE post_type = %s
                                 AND post_status NOT IN ( '" . implode(
 "','", $exclude_states ) . "' )
                                 AND post_author = %d",
                                 $post_type,
                                 get_current_user_id()
                         )
                 );
 }}}

 In the SPX captured request it indicated that query was taking 0.360
 seconds.  That was more than 40% of the total time to request the page.

 To isolate just the query, I started running it directly against MySQL.
 In my case that was:

 {{{
 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
 }}}

 Doing that 10 times gave me a p75 response time of 0.346 seconds ( min:
 0.341 seconds, max: 0.356 seconds ).  That confirms that this query alone
 was taking up a big chunk of time to finish the request.

 Adding `EXPLAIN` to that query reported:

 - id: 1
 - select_type: SIMPLE
 - table: wp_posts
 - partitions: NULL
 - type: ref
 - possible_keys: type_status_date,post_author
 - key: post_author
 - key_len: 8
 - ref: const
 - rows: 145,414
 - filtered: 50.04
 - extra: Using where

 Trying a few different things, I eventually landed on this new index:

 {{{
 CREATE INDEX type_status_author ON wp_posts (post_type, post_status,
 post_author);
 }}}

 Running the same `COUNT( 1 )` query another 10 times gave a new p75
 response time of 0.123 seconds ( min: 0.119 seconds, max: 0.125 seconds ).
 Doing `EXPLAIN` on the query with the new index in place reported:

 - id: 1
 - select_type: SIMPLE
 - table: wp_posts
 - partitions: NULL
 - type: range
 - possible_keys: type_status_date,post_author,type_status_author
 - key: type_status_author
 - key_len: 164
 - ref: NULL
 - rows: 145,520
 - filtered: 50.00
 - extra: Using where; Using index

 To confirm the impact that this would have on the full `All Posts` ( `/wp-
 admin/edit.php` ) HTTP request I ran ten curl requests for it before and
 after.  This is not TTFB, it was the total time the curl request took.

 - p75 BEFORE: 0.826 seconds ( min: 0.812 seconds, max: 0.829 seconds )
 - p75 AFTER: 0.558 seconds ( min: 0.550 seconds, max: 0.563 seconds )

 That confirmed that adding the index, speeding up that single query,
 reduced the time for the `All Posts` HTTP page request significantly.
 Speeding up a request by 32% doesn't come along every day.

 Adding an index does come with some trade offs.  Inserting into the
 `wp_posts` table will require an update to the new index.  In my test
 setup I found that inserts took about 0.0001 seconds longer at p75 with
 the new index in place.  That seems small enough that I don't think it is
 a concern.

 The other part of having a new index is space.  I ran the following query
 to get a list of the index sizes ( in MB ) for the `wp_posts` table:

 {{{
 SELECT
     database_name,
     TABLE_NAME,
     index_name,
     ROUND(
         stat_value * @@innodb_page_size / 1024 / 1024,
         2
     ) size_in_mb
 FROM
     mysql.innodb_index_stats
 WHERE
     stat_name = 'size' AND index_name != 'PRIMARY' AND database_name =
 'wordpress' AND TABLE_NAME = 'wp_posts'
 ORDER BY
     size_in_mb
 DESC;
 }}}


 - post_name: 30.6 MB
 - type_status_date: 18.5 MB
 - type_status_author: 13.5 MB
 - post_author: 7.5 MB
 - post_parent: 7.5 MB

 Adding 13.5 MB to the set of indexes is a sizable increase.  But in the
 bigger picture, the `wp_posts` table is also 500 MB.  Making a 13.5 MB new
 index a 2.7% increase.

 All in all, I think the trade off of having a new index is worth the
 improvement.

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