I am working on a task where i need to retrive 100s posts under single-page/single-request with their featured images.

By using wordpress method of retriving posts and then retrive featured image individually using get_the_post_thumbnail function it takes so much time to load the page.

Can someone provide a faster solution for this like retrive posts and featured image under one single query. That should speed up the proccess.

I am working on a task where i need to retrive 100s posts under single-page/single-request with their featured images.

By using wordpress method of retriving posts and then retrive featured image individually using get_the_post_thumbnail function it takes so much time to load the page.

Can someone provide a faster solution for this like retrive posts and featured image under one single query. That should speed up the proccess.

• php
• posts
• mysql
• single

• 1 Can you post the code you have at the moment - it maybe possible to improve what you have already. – Aravona Commented May 11, 2017 at 7:58
• First of all I'd recommend implementing some pagination to drastically reduce your query size with posts_per_page. Seeing your current code would really help. – Tim Hallman Commented May 16, 2017 at 16:33
• Also, have a look at doing a meta_query. You should avoid direct SQL queries whenever possible. With a meta query you could retrieve all your posts and then use the IDs to query all the post thumbnails at the same time. – Tim Hallman Commented May 16, 2017 at 16:43

5 Answers 5

Worked on similar problem recently. Here is the SQL query to get the post with Featured Image.

global $wpdb;

$perpage = 10; 
$page = 1; // Get the current page FROM $wp_query

$counter = $perpage * $page;

$uploadDir = wp_upload_dir();
$uploadDir = $uploadDir['baseurl'];

$sql = "
SELECT 
    post.ID,
    post.post_title,
    post.post_date,
    post.category_name,
    post.category_slug,
    post.category_id,
    CONCAT( '".$uploadDir."','/', thumb.meta_value) as thumbnail,
    post.post_type
FROM (
    SELECT  p.ID,   
          p.post_title, 
          p.post_date,
          p.post_type,
          MAX(CASE WHEN pm.meta_key = '_thumbnail_id' then pm.meta_value ELSE NULL END) as thumbnail_id,
      term.name as category_name,
      term.slug as category_slug,
      term.term_id as category_id
    FROM ".$wpdb->prefix."posts as p 
    LEFT JOIN ".$wpdb->prefix."postmeta as pm ON ( pm.post_id = p.ID)
    LEFT JOIN ".$wpdb->prefix."term_relationships as tr ON tr.object_id = p.ID
    LEFT JOIN ".$wpdb->prefix."terms as term ON tr.term_taxonomy_id = term.term_id
    WHERE 1 ".$where." AND p.post_status = 'publish'
    GROUP BY p.ID ORDER BY p.post_date DESC
  ) as post
  LEFT JOIN ".$wpdb->prefix."postmeta AS thumb 
    ON thumb.meta_key = '_wp_attached_file' 
    AND thumb.post_id = post.thumbnail_id
  LIMIT ".$counter.",".$perpage;

$posts = $wpdb->get_results( $sql, ARRAY_A); 

Bonus : You will also get Category details with post details if you need.

P.S : You will need to change the query a bit to match your requirements and get desired fields.

This is much simpler solution without any use of complex joins,

SELECT wp_posts.id,
       wp_posts.post_title,
       wp_terms.name,
       (SELECT guid
        FROM   wp_posts
        WHERE  id = wp_postmeta.meta_value) AS image
FROM   wp_posts,
       wp_postmeta,
       wp_term_relationships,
       wp_terms
WHERE  wp_posts.id = wp_term_relationships.object_id
       AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id
       AND wp_terms.name = 'mycat'
       AND wp_posts.post_status = "publish"
       AND wp_posts.post_type = "post"
       AND wp_postmeta.post_id = wp_posts.id
       AND wp_postmeta.meta_key = '_thumbnail_id'
ORDER  BY wp_posts.post_date DESC
LIMIT  5;

This query will give post id, post category, and featured image. You can filter the category by changing wp_terms.name = 'mycat' with your category name.

The post and featured image URL is saved in wp_posts table and its relation is saved in wp_postmeta table so in any case you have to query both these tables either directly in single Query or using WordPress function and query them separately.

I don't think querying both tables in single Query will improve major performance but if you want to do it then you can use below custom code.

    global $wpdb;
    $results = $wpdb->get_results( "SELECT * FROM $wpdb->posts, $wpdb->postmeta where $wpdb->posts.ID = $wpdb->postmeta.post_id and $wpdb->postmeta.meta_key = '_thumbnail_id' and $wpdb->posts.post_type='post' limit 100");

    if ( $results )
    {
        foreach ( $results as $post )
        {       
            setup_postdata( $post );
            ?>
            <h2>
                <a href="<?php the_permalink(); ?>" rel="bookmark" title="Permalink: <?php the_title(); ?>">
                    <?php the_title(); ?>
                </a>
            </h2>
            <?php
            if ( $post->meta_value ) { 
                $image = image_downsize( $post->meta_value );
                ?>
                <img src="<?php echo $image[0]; ?>" />
                <?php
            }
        }   
    }
    else
    {
        ?>
        <h2>Not Found</h2>
        <?php
    } 

You can manage the page load speed by using infinite scroll method. Retrieve only those post which are showing above the fold and on scroll you can query other posts this can help you load your page much more quickly. Here is a tutorial for this.

https://code.tutsplus.com/tutorials/how-to-create-infinite-scroll-pagination--wp-24873

There are some plugins for infinite scroll for posts.

https://wordpress.org/plugins/wp-infinite-scrolling/

The accepted answer will ruin your life LOL... Here's the easy way..

global $wpdb;

$uploadDir = wp_upload_dir();
$uploadDir = $uploadDir['baseurl'];

$page = (!empty($_REQUEST['p'])) ? $_REQUEST['p'] : 1;
$_limit = 50;
$_start = ($page > 1) ? ($page * $_limit) : 0;

$_stmts = "
    SELECT 
        p.ID, 
        p.post_title,
        p.post_name,
        CONCAT( '".$uploadDir."', '/', thumb.meta_value) as thumbnail
    FROM {$wpdb->prefix}posts AS p
        
        LEFT JOIN {$wpdb->prefix}postmeta AS thumbnail_id
            ON thumbnail_id.post_id = p.ID AND thumbnail_id.meta_key = '_thumbnail_id'
        
        LEFT JOIN {$wpdb->prefix}postmeta AS thumb
            ON thumb.post_id = thumbnail_id.meta_value AND thumb.meta_key = '_wp_attached_file'
        
        
    WHERE p.post_status = 'publish'
    LIMIT {$_start},{$_limit}
";

$get_rs = $wpdb->get_results($_stmts);