Make WordPress Core

Opened 16 months ago

#57303 new enhancement

wp_get_posts() for post_type="any" + post__in creates not optimized database query

Reported by: madeinua's profile madeinua Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 6.2
Component: Posts, Post Types Keywords: has-patch
Focuses: performance Cc:

Description

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
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!

Attachments (1)

all-wp-query.patch (4.6 KB) - added by madeinua 16 months ago.

Download all attachments as: .zip

Change History (1)

Note: See TracTickets for help on using tickets.