[wp-trac] [WordPress Trac] #57303: wp_get_posts() for post_type="any" + post__in creates not optimized database query

[WordPress Trac]

#57303: wp_get_posts() for post_type="any" + post__in creates not optimized
database query
-------------------------------+-----------------------------
 Reporter:  madeinua           |      Owner:  (none)
     Type:  enhancement        |     Status:  new
 Priority:  normal             |  Milestone:  Awaiting Review
Component:  Posts, Post Types  |    Version:  trunk
 Severity:  normal             |   Keywords:  has-patch
  Focuses:  performance        |
-------------------------------+-----------------------------
 Hi all.

 I have a case where the database query is not very optimal and (probably)
 needs to be improved.

 Case: I have a list of post ids that belong to several different post
 types. Now I need to get the WP_Post objects for this list of posts. So I
 do the following:

 {{{#!php
 <?php
 get_posts([
   'post_type' => 'any',
   'post__in' => [10,20,30,40,50]
 ]);
 }}}

 This code works well, however I have a lot of different post types (15+)
 on my site and as a result the database query looks like this:

 {{{
 SELECT wp_posts.*
 FROM wp_posts
 WHERE 1=1
 AND ((wp_posts.post_type = ? AND (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)) OR (wp_posts.post_type = ? AND
 (wp_posts.post_status = ?
 OR wp_posts.post_status = ?)))
 WHERE wp_posts.ID IN (?)
 GROUP BY wp_posts.ID
 ORDER BY FIELD(wp_posts.ID,?,?)
 }}}

 As you can see, it checks post_type + post_status for every public post
 type on my site. But the wp_get_posts() attributes explicitly declare a
 list of ids and that the post type can simply be ignored in this case
 (because the post_type is set to "any").

 The relevant query should be like this (because it covers all
 requirements):

 {{{
 SELECT wp_posts.*
 FROM wp_posts
 WHERE 1=1
 AND (wp_posts.post_status = ?)
 WHERE wp_posts.ID IN (?)
 GROUP BY wp_posts.ID
 ORDER BY FIELD(wp_posts.ID,?,?)
 }}}

 I checked class-wp-query.php and didn't find a solution without making
 changes to the WordPress code. There may already be a workaround
 implemented - it would be nice to know it. But this can be easily
 implemented by adding something like post_type="all" (similar to "any").
 The implementation of the idea has been added to the patch file
 (attached).

 Thanks in advance!

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/57303>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform