[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:  6.9
Component:  Posts, Post Types   |     Version:  5.4.1
 Severity:  normal              |  Resolution:
 Keywords:  has-patch 2nd-      |     Focuses:  administration, performance
  opinion                       |
--------------------------------+------------------------------------------

Comment (by josephscott):

 @siliconforks I've been trying to reproduce this issue -

 > 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

 But so far have not been able to.  I tried it with a quick Docker
 approach:

 > docker run --name test-mysql-server -e MYSQL_ROOT_PASSWORD=--password--
 -d mysql:8.0.42

 And then I spun up an Ubuntu 22.04 container, brought it up to date and
 did `apt-get install mysql-server` to get a server.

 > $ dpkg -l |grep mysql-server
 > ii  mysql-server                  8.0.42-0ubuntu0.22.04.2
 all          MySQL database server (metapackage depending on the latest
 version)
 > ii  mysql-server-8.0              8.0.42-0ubuntu0.22.04.2
 arm64        MySQL database server binaries and system database setup
 > ii  mysql-server-core-8.0         8.0.42-0ubuntu0.22.04.2
 arm64        MySQL database server binaries

 This was on my MacBook Pro, so arm64 versions all the way around.  I made
 no changes to the MySQL server config.

 {{{
  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: range
  possible_keys: type_status_date,post_author,type_status_author
           key: type_status_author
        key_len: 164
           ref: NULL
          rows: 30
      filtered: 39.64
         Extra: Using where; Using index
  1 row in set, 1 warning (0.00 sec)
 }}}

 My first thought on why this might be happening was different in query
 optimizers between MySQL versions, but that doesn't appear to be the case.

 I used a smaller data set, to make it easier to quickly import into
 different environments.  This reduced `wp_posts` table only has 1,848
 rows.

 Not sure what is causing the difference in index use.  If you have any
 ideas I am happy to try them out.  Better yet, if you can reproduce it
 with a Docker image that would be great.  Then I could confirm if this is
 a version specific issue.

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