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

 Replying to [comment:12 johnjamesjacoby]:
 > My guess is MySQL's query optimizer gives up after a certain number of
 `NOT IN`'s relative to the cardinality of the data in the `post_status`
 column, and that including & excluding fewer statuses would usually help.

 It seems like it refuses to use the index whenever there's a `NOT IN`.
 Even if it's just one item, like `NOT IN ( 'trash' )`, it still doesn't
 use the index.  The only way I can get it to use the index is to specify
 the index explicitly:

 {{{
 SELECT COUNT( 1 )
 FROM wp_posts
 USE INDEX (type_status_author)
 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
 }}}

 Then it does use the index.

 > How many posts did you test this with?

 I tried to make it roughly the same as @josephscott did with 340,000 blog
 posts.

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