1

I am wondering if querying posts like this:

    <?php $args = array('numberposts' => 19, 'orderby' => 'rand' ); 
    $posts = get_posts( $args );
    foreach($posts as $post) {  
        $url = get_the_title($post->ID);
                echo '<span class="url">'.$image_url.'</span>';
                echo '<span class="ID">'.$post->ID.'</span>';  
    } 
    ?>

will cause any performance issues if I have over 4000 posts? I am not sure how the query works, but I imagine that it works by retrieving all 4000 posts, sorting them all into random positions and then lastly picks the first 19. It sounds kinda heavy hence my question.

P.S. 4000+ posts might sound a lot, but they are actually just link posts with empty content and href's in the title.

EDIT: Final code from the answer (untested)

<?php

  $randposts = $wpdb->get_results( 
     "
     SELECT SQL_CALC_FOUND_ROWS wp_posts.*
     FROM wp_posts 
     WHERE 1=1 
         AND wp_posts.post_type = 'post' 
         AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
     ORDER BY RAND() DESC 
     LIMIT 0, 19
     "
 );

 foreach($randposts as $post) {  
     $url = get_the_title($post->ID);
     echo '<span class="url">'.$image_url.'</span>';
     echo '<span class="ID">'.$post->ID.'</span>';  
 } 
 ?>
  • 1
    You can check the DB queries by enabling debugging and using something like wordpress.org/plugins/debug-bar, not every set-up is the same, so the queries might be different per environment. – Wyck May 17 '14 at 16:18
  • 1
    It's meaningful to say that random order is prone to performance issues by nature, but impossible to say if it's an actual issue for your content and environment without testing. – Rarst May 17 '14 at 17:25
  • I downloaded the plugin but I am unsure what I should look for. Should I check the plugin data, remove the random query from my site, check again and compare the numbers? – Tony Fire May 17 '14 at 19:42
1

The "Order By Rand() Alternative" method

You can check out this article that discuss how to optimize SQL queries when selecting N random rows. It's called Order By Rand() Alternative method.

I've used it in one of my previous answers.

As far as I understand it, the trick is to introduce a special WHERE condition to cut down the rows before the slow ORDER BY RAND() kicks in. In your case it could be something like:

WHERE RAND() < ( SELECT ( ( 19 / COUNT(1) ) * 10 ) FROM wp_posts )

where COUNT(1) should be faster than COUNT(*) on InnoDB.

One can use this trick by modifying the SQL generated from the WP_Query(), through the posts_request filter.

This might not be relevant for only 4000 posts, but this might be useful when dealing with hundreds of thousands of posts.

To address your question, I would recommend you to profile your queries, with - and without the random ordering, just as @Wyck proposed in a comment.

If you use numberposts equal to 19, then you will only fetch 19 rows from the posts table, but not 4000. On the other hand if numberposts is equal to -1, your query will fetch the whole table.

The generated SQL from your get_posts( $args ) query will probably look something like this:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
    FROM wp_posts 
    WHERE 1=1 
        AND wp_posts.post_type = 'post' 
        AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
    ORDER BY RAND() DESC 
    LIMIT 0, 19

where the parameters numberposts (or posts_per_page) and paged control the limit part of the generated query.

If you want to test the trick, try:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
    FROM wp_posts 
    WHERE 1=1 
        AND wp_posts.post_type = 'post' 
        AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
        AND RAND() < ( SELECT ( ( 19 / COUNT(1) ) * 10 ) )
    ORDER BY RAND() DESC 
    LIMIT 0, 19

You could, for example, test the restriction explicitly with:

SELECT wp_posts.*
    FROM wp_posts 
    WHERE wp_posts.post_type = 'post' 
        AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
        AND RAND() < 0.0475
    ORDER BY RAND() DESC 
    LIMIT 0, 19

where ( 19 / 4000 ) * 10 ) = 0.0475. But this is just an example that you can play with further and adjust to your needs.

  • I see. So if there's a performance issue it would probably be caused by "Order by rand" and not the fetching of rows itself? And are these 19 posts fetched randomly from the table by default? Or are they the same 19 posts sorted randomly through Order By Rand()? – Tony Fire May 17 '14 at 19:40
  • The default order is by post date (DESC), so no random there. It's difficult to generalize on performance, because your database might, for example, be on another server, so latancy could be the bottleneck. Profile your query, and compare it to another one without a random ordering. – birgire May 17 '14 at 21:23
  • Say, would using Google's Pagespeed Insight be a able to tell/indicate that the query is giving me performance issue? – Tony Fire May 17 '14 at 21:34
  • Indirectly, if it's slowing down your page loads. But it doesn't tell you what query. – birgire May 17 '14 at 22:15
  • I see, thanks for the clarification. I've updated the question with the final code, would you mind taking a look? I've never used SQL queries in this way before. – Tony Fire May 17 '14 at 22:37
0

get_posts is executed as a single SELECT query on WordPress DB. So pulling 19 random posts from 4000+ posts will not cause any performance issues, it's almost similar to pulling 19 posts from 100 posts blog.

  • 2
    This isn't really correct due to the nature of a MySQL ORDER BY RAND(). Run a few queries and watch the execution time. For example, on a table of 10,000 or so records I get about the same execution time with ORDER BY RAND() and a limit of 10, 100, and 1000-- about 0.025 seconds vs. about 0.0005 without the ORDER BY RAND(). And a RAND() query can never cache, at least not via MySQL's query cache. – s_ha_dum May 17 '14 at 18:56
  • Yeah, I noticed that, thanks for replying. But I guess, using get_posts will keep the code maintainable, as WordPress itself used WP_Query for almost all the loops. – Tejaswini May 19 '14 at 6:47
  • It is not a matter of maintainable or not. It is a matter of "So pulling 19 random posts from 4000+ posts will not cause any performance issues" being false. – s_ha_dum May 19 '14 at 12:34

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.