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

 > 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.)

 The "Mine" queries are per-user & per-post-type, so the only place to keep
 it is `usermeta`. I don't really consider it viable though, because of the
 overhead from meta writes & user meta/object cache invalidation for every
 post-status change.

 (bbPress does this using the user-options API – see
 `bbp_get_user_topic_count_raw()` for a similar query – but only with the
 knowledge & vision that there are very few multi-forum setups out there to
 overpopulate `usermeta` the way multisite/blogs/posts would.)

 We could, at the very least, add caching to these Mine queries, but that
 would require more PHP than just adding the index via SQL, and isn't
 universally useful to plugins, custom post types & statuses, etc...

 > The other counts are not fast either, so it would be good to be able to
 skip those as well.

 Or add an index for them, too? 😅

 Actually, the SQL inside of `wp_count_posts()` could probably be rewritten
 to leverage this new index, as it's the same `post_type`, `post_status`,
 `post_author` combo thanks to `readable` being used.

 > Granted, that might just be some bug in older MySQL versions

 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.

 > I tested this on a Ubuntu 22.04 machine with MySQL
 8.0.42-0ubuntu0.22.04.2
 > it is still likely to get slower and slower as more and more blog posts
 are added to the table.

 How many posts did you test this with? I think the more posts there are,
 the more useful this index becomes relative to the existing slowness?

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