3

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.

3
  • 1
    Can you post the code you have at the moment - it maybe possible to improve what you have already.
    – Aravona
    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. 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. May 16, 2017 at 16:43

5 Answers 5

2
+25

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.

6
  • Using SQL queries like this in Wordpress should really be avoided May 16, 2017 at 16:30
  • Yes but this will dramatically reduce the response time. because it reduces the overhead of sending queries back and forth to the database. May 16, 2017 at 16:43
  • It is not necessary to keep sending queries. Wordpress has native support for handling this. Consider meta_query May 16, 2017 at 16:44
  • Honestly though, I think the OP's real issue here is pagination. I doubt the query is what is actually taking so long... the page probably isn't rendering fast because he's loading up hundreds of images. May 16, 2017 at 16:46
  • Yes. As suggested in other answers he can implement lazy loading and infinite scroll as well. May 16, 2017 at 17:06
3

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.

2

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
    } 
1

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/

1

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);
2
  • Can you explain what's wrong with it please, and what yours does better? At first glance it looks like you're just omitting the categories join.
    – Rup
    Mar 1 at 12:12
  • @Rup, first try to count how many lines you have to write just for that easy thing, try to put yourself as a beginner, do you think you will like it? The sub SQL query method is only applicable if you cannot accomplish it with direct SQL statement. Let's try to post answer that is user friendly. Mar 4 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.