[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):

 After more testing ( and some suggestions and experiments from @mreishus )
 I think what we have settled on is that use of the new
 `type_status_author` can be data dependent.  I don't think this is a
 problem, we should let the MySQL query optimizer pick the index it thinks
 is best given the conditions of the data and database.

 For those that might want to follow along, prepare to go down the rabbit
 hole.  I have a GH repo - https://github.com/josephscott/wporg-mysql-tsa-
 index - that explores a few different conditions and data sets to show
 when the new index does and does not get used.  All of the tests in the
 repo are making use of MySQL 8.0.42.

 The only requirements are Docker and PHP.  Most of the tests are run
 against a subset of my previously generated hundreds of thousands of
 posts.  These work as expected.  An alternate dataset ( from @mreishus )
 provide conditions where the new `type_status_author` does not get used.
 There are also variations on when the new index is added and running
 analyze on the table.

 Here is the output of the existing tests:

 {{{
 $ php tests.php

 ***** Test: 1 *****
 > NO TSA index

 MySQL version: 8.0.42
 dropping table: wp_posts
 running: create-table.sql
 running: autoinc.sql
 running: wp-posts-data.sql
 Using index: type_status_date


 ***** Test: 2 *****
 > New TSA index immediately after create table, before insert

 MySQL version: 8.0.42
 dropping table: wp_posts
 running: create-table.sql
 running: autoinc.sql
 running: tsa-index.sql
 running: wp-posts-data.sql
 Using index: type_status_author


 ***** Test: 2b *****
 > New TSA index immediately after create table, before insert (alt data
 set)

 MySQL version: 8.0.42
 dropping table: wp_posts
 running: create-table.sql
 running: autoinc.sql
 running: tsa-index.sql
 running: wp-posts-data-alt.sql
 Using index: post_author


 ***** Test: 3 *****
 > New TSA index immediately after create table, then analyze, before
 insert

 MySQL version: 8.0.42
 dropping table: wp_posts
 running: create-table.sql
 running: autoinc.sql
 running: tsa-index.sql
 running: analyze-wp-posts.sql
 running: wp-posts-data.sql
 Using index: type_status_author


 ***** Test: 4 *****
 > New TSA index after inserting data

 MySQL version: 8.0.42
 dropping table: wp_posts
 running: create-table.sql
 running: autoinc.sql
 running: wp-posts-data.sql
 running: tsa-index.sql
 Using index: type_status_author
 }}}

 There is also a verbose option that will provide the full EXPLAIN
 key/value pairs.  For this test I was only concerned about the specific
 index that was being used.

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