WordPress.org

Ready to get started?Download WordPress

Forums

Is there a faster way to display random link to post? (5 posts)

  1. maerk
    Member
    Posted 8 years ago #

    I'm using the following snippet to get a random post from the database, and then display a link to it.

    $neon_story_ID = $wpdb->get_var("SELECT ID FROM $wpdb->posts WHERE post_status = 'static' AND post_parent = '2' ORDER BY RAND() LIMIT 1");

    $neon_story = $wpdb->get_row("SELECT post_name, post_title FROM $wpdb->posts WHERE ID = $neon_story_ID", ARRAY_A);

    Is this the most optimal way to do things? Could it be made more efficient?

  2. davidchait
    Member
    Posted 8 years ago #

    This has been covered at some point...

    ORDER BY RAND() is extremely ineffecient. I've seen queries with subqueries to get around the issue. I myself run a query for some number of IDs (say the past 100), then do the rand in PHP.

    You could also cache results to make this even faster. For instance, grab the post ID, name, and title for the last 100 posts. Cache that to disk in some file. Now, if the file exists (and hasn't 'expired' due to whatever time check you want to do), read the file, run a rand() in PHP, and grab one element for display.

    -d

  3. Samuel Wood (Otto)
    Tech Ninja
    Posted 8 years ago #

    david is correct. Order by rand() is very slow on large tables. This isn't necessarily a mySQL specific problem either, happens on lots of databases. There's several faster ways to do it.

    The usual approach is to:
    - Find out how many rows are in the table, call it X,
    - Pick a random number between 1 and X,
    - Get the Xth row.

    Something similar to this should work a bit quicker:
    $num_rows = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'static' AND post_parent = '2'");
    $random_number = mt_rand(1, $num_rows);
    $neon_story = $wpdb->get_row("SELECT post_name, post_title FROM $wpdb->posts WHERE post_status = 'static' AND post_parent = '2' LIMIT $random_number,1", ARRAY_A);

  4. davidchait
    Member
    Posted 8 years ago #

    Yep, thats one of the alternate approaches to do it (mostly) via the DB. Again though, on a shared server environment, you would be well served to build a cache of posts to pick from, and pick from that list rather than a dynamic query per pageview. Even though the above does a fairly-optimal breakdown (a count* and a select-limit-1), my guess is it can still be of impact.

  5. maerk
    Member
    Posted 8 years ago #

    Thanks, this is great stuff. At the moment I'll use Otto's solution, but I'll look into using a cache.

Topic Closed

This topic has been closed to new replies.

About this Topic