Suggestions on making this querycode more performant

I am working on a shortcode that will create and grid of all custom posts of a certain type. This is working all well, if I get only the post titles. However, if I add the post thumbnails, I move from making about 28 queries to about 1000 queries.

When looking at the queries through Query Monitor I can see that the call to get_the_post_thumbnail( $post->ID, 'medium' ) is duplicated 328 times. Plus it calls wp_attachment_is(), wp_get_attachment_url() and wp_get_attachment_metadata()

This is the important part of the code

$args   = array(
    'post_type'      => 'zxy_movie',
    'post_status'    => 'publish',
    'orderby'        => 'post_title',
    'order'          => 'ASC',
    'posts_per_page' => - 1,
);
$posts = get_posts( $args ); // 332

$output = '';

if ( $posts && count( $posts ) > 0 ) {
    foreach ( $posts as $post ) {
        $output .= '<div class="zxy-item">';
        $output .= '<a href="' . get_permalink( $post->ID ) . '">';
        $output .= '<div class="zxy-image">';
        $output .= get_the_post_thumbnail( $post->ID, 'medium' );
        $output .= '</div>';
        $output .= '<div class="zxy-title">';
        $output .= $post->post_title;
        $output .= '</div>';
        $output .= '</a>';
        $output .= '</div>';

    }
}

return $output;

So can anyone here provide me with any tip, tricks, ideas and/or solutions to reduce the number of queries needed to get the post thumbnail image.

I am working on a shortcode that will create and grid of all custom posts of a certain type. This is working all well, if I get only the post titles. However, if I add the post thumbnails, I move from making about 28 queries to about 1000 queries.

When looking at the queries through Query Monitor I can see that the call to get_the_post_thumbnail( $post->ID, 'medium' ) is duplicated 328 times. Plus it calls wp_attachment_is(), wp_get_attachment_url() and wp_get_attachment_metadata()

This is the important part of the code

$args   = array(
    'post_type'      => 'zxy_movie',
    'post_status'    => 'publish',
    'orderby'        => 'post_title',
    'order'          => 'ASC',
    'posts_per_page' => - 1,
);
$posts = get_posts( $args ); // 332

$output = '';

if ( $posts && count( $posts ) > 0 ) {
    foreach ( $posts as $post ) {
        $output .= '<div class="zxy-item">';
        $output .= '<a href="' . get_permalink( $post->ID ) . '">';
        $output .= '<div class="zxy-image">';
        $output .= get_the_post_thumbnail( $post->ID, 'medium' );
        $output .= '</div>';
        $output .= '<div class="zxy-title">';
        $output .= $post->post_title;
        $output .= '</div>';
        $output .= '</a>';
        $output .= '</div>';

    }
}

return $output;

So can anyone here provide me with any tip, tricks, ideas and/or solutions to reduce the number of queries needed to get the post thumbnail image.

• wp-query
• meta-query

Share Improve this question asked Jan 23, 2019 at 13:56 wzywzy 1122 bronze badges 5

• How many posts do you have exactly? – Jacob Peattie Commented Jan 23, 2019 at 13:59
• 1 Is it 332? I just noticed the comment in your code. If you ask me that's just too many posts to query at once, and too many images to display at once, but I don't know the full context. The fact is that get_the_post_thumbnail() requires a query to the database to get the ID of the attachment, and then another query or two to get all the information about that attachment, including the URL, dimensions, alt text etc. It's a lot of information to output onto a page, so it takes a fair bit to query it all, especially given WordPress' db structure. – Jacob Peattie Commented Jan 23, 2019 at 14:10
• 1 You could maybe pull it all off with a single custom SQL query, but that's going to involve a lot of joins and probably won't be much faster. Plus you'd have to write code to do what WordPress already does, but on the data structure you've returned, such as figuring out an attachment's URL and HTML tags, since you'd need to avoid using the built-in functions so that WordPress doesn't query the database again. 300+ images is a lot, especially at medium size, so the front-end is probably going to be slow anyway. My advice is just to paginate. – Jacob Peattie Commented Jan 23, 2019 at 14:13
• It is a part of a shortcode function create an index page of all posts of a certain type. Given the nature of getting a post thumbnail I think I'll just get the post title plus cacheing the data with a transient – wzy Commented Jan 23, 2019 at 15:03
• Maybe benchmark the difference between including 'order'=> 'ASC', and leaving it as the default of DESC but reversing your loop using php. You might need to increase the number of posts queried to 1000+ to see any difference though. (you would use a for ($i in) loop rather than a foreach to reverse the output order) – admcfajn Commented Jan 23, 2019 at 23:15

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

In answering my own question I have decide to go the pure SQL query route as WP runs too many queries in fetching a post and its attached post thumbnail, in addition to fetching and building meta for that thumbnail.

Here is the code I ended up with:

$query2 = "SELECT post.ID, post.post_title, thumbnail_url.meta_value as thumbnail_url 
           FROM $wpdb->posts as post 
           LEFT OUTER JOIN $wpdb->postmeta as thumbnail 
           ON post.ID = thumbnail.post_id AND thumbnail.meta_key = '_thumbnail_id' 
           LEFT OUTER JOIN $wpdb->postmeta as thumbnail_url 
           ON thumbnail.meta_value = thumbnail_url.post_id 
           AND thumbnail_url.meta_key = '_wp_attached_file' 
           WHERE 1 = 1 
           AND post.post_type = 'zxy_movie' 
           AND post.post_status = 'publish' 
           ORDER BY post.post_title ASC";

 $posts = $wpdb->get_results( $query2, OBJECT );

Please note: This is still and expensive query and I do not recommend it on large sites.