get_adjacent_post alternative on a very large db

Question

I'm using get_next_post and get_previous_post (from the same category) to show the thumbnail and link to the respectives posts in my single template, but it's in a very large db that is giving my server a hard time.

There are 49, 984 posts in the site, between published posts, drafts and attachments:

mysql> select post_status, count(1) from wp_posts group by post_status;
+-------------+----------+
| post_status | count(1) |
+-------------+----------+
| auto-draft  |        1 |
| draft       |      269 |
| inherit     |    38656 |
| private     |        5 |
| publish     |    11053 |
+-------------+----------+
5 rows in set (0,07 sec)

I can understand why get_previous_post takes so much resources to run, after all, it has to compare all matching posts by date to determine the next or previous:

From $wdbp->queries, just to fetch the previous post:

Query:

SELECT p.id
FROM wp_posts AS p 
  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id 
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    AND tt.taxonomy = 'category' AND tt.term_id IN (5,7,14474) 
WHERE
  p.post_date < '2013-04-11 12:15:30' AND p.post_type = 'post' AND
  p.post_status = 'publish' AND tt.taxonomy = 'category'

ORDER BY p.post_date DESC LIMIT 1

From:

  require('wp-blog-header.php'),
  require_once('wp-includes/template-loader.php'),
  include('/themes/extra/single.php'),
  get_previous_post, get_adjacent_post

Taking:

111.7ms

I know doing that it's not usually a hard job, but in my case it is.

Is there any other way to get the previous and next post?

Edit:

As s_ha_dum points out, including the condition $in_same_cat it's the source of the problem, by doing two very heavy joins with wp_term_relationships and wp_term_taxonomy. The solution would be to not search for posts in the same category, unfortunately I do need to use that condition.

Solution edit:

Here is a gist with toscho's solution plus a minor fix and two new functions: get_fast_previous_post and get_fast_next_post to get the post objects.

Answer 1

I don’t see a way to make the query faster, but we can cache the result.

Unfortunately, there is no hook to circumvent next_post_link() and previous_post_link(), so we have to replace those functions with custom functions.

The following sample code uses a post meta field to store the result. There might be side effects – I tried to cover most cases, but might have missed something.

<?php # -*- coding: utf-8 -*-
namespace WPSE;
/* Plugin Name: Fast Next/Prev Post Links
 * Plugin URI:  https://wordpress.stackexchange.com/q/101435/
 */

add_action(
    'fast_prev_post_link',
    __NAMESPACE__ . '\fast_prev_post_link',
    10,
    4
);
add_action(
    'fast_next_post_link',
    __NAMESPACE__ . '\fast_next_post_link',
    10,
    4
);
add_action(
    'transition_post_status',
    __NAMESPACE__ . '\delete_fast_adjacent_meta',
    10,
    3
);


/**
 * Print previous post link.
 *
 * @param unknown_type $format
 * @param unknown_type $link
 * @param unknown_type $in_same_cat
 * @param unknown_type $excluded_categories
 */
function fast_prev_post_link(
    $format              = '&laquo; %link',
    $link                = '%title',
    $in_same_cat         = FALSE,
    $excluded_categories = ''
    )
{
    empty ( $format ) && $format = '%link &raquo;';
    fast_adjacent_post_link(
        $format,
        $link,
        $in_same_cat,
        $excluded_categories,
        TRUE
    );

}
/**
 * Print next post link.
 *
 * @param  string $format
 * @param  string $link
 * @param  bool $in_same_cat
 * @param  array|string $excluded_categories
 * @return void
 */
function fast_next_post_link(
    $format              = '%link &raquo;',
    $link                = '%title',
    $in_same_cat         = FALSE,
    $excluded_categories = ''
    )
{
    empty ( $format ) && $format = '%link &raquo;';

    fast_adjacent_post_link(
        $format,
        $link,
        $in_same_cat,
        $excluded_categories,
        FALSE
    );
}

/**
 * Display adjacent post link.
 *
 * Slightly changed copy of adjacent_post_link().
 * Unfortunately, WP mixes retrieving data and display. :(
 *
 * Can be either next post link or previous.
 *
 * @param  string       $format              Link anchor format.
 * @param  string       $link                Link permalink format.
 * @param  bool         $in_same_cat         Whether link should be in a same
 *                                           category.
 * @param  array|string $excluded_categories Array or comma-separated list of
 *                                           excluded category IDs.
 * @param  bool         $previous            Default is true. Whether to display
 *                                           link to previous or next post.
 * @return void
 */
function fast_adjacent_post_link(
    $format,
    $link,
    $in_same_cat         = FALSE,
    $excluded_categories = '',
    $previous            = TRUE
    )
{
    if ( $previous && is_attachment() )
        $post = get_post( get_post()->post_parent );
    else // the only real change
        $post = get_fast_adjacent_post(
            $in_same_cat,
            $excluded_categories,
            $previous
        );

    if ( ! $post ) {
        $output = '';
    } else {
        $title = $post->post_title;

        if ( empty( $post->post_title ) )
            $title = $previous ? __( 'Previous Post' ) : __( 'Next Post' );

        $title = apply_filters( 'the_title', $title, $post->ID );
        $date = mysql2date( get_option( 'date_format' ), $post->post_date );
        $rel = $previous ? 'prev' : 'next';

        $string = '<a href="' . get_permalink( $post ) . '" rel="'.$rel.'">';
        $inlink = str_replace( '%title', $title, $link );
        $inlink = str_replace( '%date', $date, $inlink );
        $inlink = $string . $inlink . '</a>';

        $output = str_replace( '%link', $inlink, $format );
    }

    $adjacent = $previous ? 'previous' : 'next';

    echo apply_filters( "{$adjacent}_post_link", $output, $format, $link, $post );
}

/**
 * Get next or previous post from post meta.
 *
 * @param bool         $in_same_cat
 * @param string|array $excluded_categories
 * @param bool         $previous
 * @param object       $post
 * @return object|NULL Either the found post object or NULL
 */
function get_fast_adjacent_post(
    $in_same_cat         = FALSE,
    $excluded_categories = array(),
    $previous            = TRUE,
    $post                = NULL
)
{
    if ( ! $post = get_post( $post ) )
        return;

    $excluded_categories = prepare_excluded_categories( $excluded_categories );

    $type     = $previous ? 'prev' : 'next';
    $cat_hash = empty ( $excluded_categories ) ? 0 : join( '-', $excluded_categories );
    $hash     = (int) $in_same_cat . "-$cat_hash";

    $meta = (array) get_post_meta( $post->ID, "_fast_{$type}_post", TRUE );

    if ( isset ( $meta[ $hash ] ) )
        return get_post( $meta[ $hash ] );

    $ad_post = get_adjacent_post( $in_same_cat, $excluded_categories, $previous );

    if ( ! $ad_post )
        return;

    $meta[ $hash ] = $ad_post->ID;
    update_post_meta( $post->ID, "_fast_{$type}_post", $meta );

    return $ad_post;
}

/**
 * Prepare categories sent as string.
 *
 * @param  string|array $cats
 * @return array
 */
function prepare_excluded_categories( $cats )
{
    if ( empty ( $cats ) or is_array( $cats ) )
        return array();

    $cats = explode( ',', $cats );
    $cats = array_map( 'trim', $excluded_categories );
    $cats = array_map( 'intval', $excluded_categories );

    return $cats;
}

/**
 * Deletes post meta values for the current post and all posts referencing it.
 *
 * @wp-hook transition_post_status
 * @param   string $new_status
 * @param   string $old_status
 * @param   object $post
 * @return  void
 */
function delete_fast_adjacent_meta( $new_status, $old_status, $post )
{
    $prev = (array) get_post_meta( $post->ID, '_fast_prev_post', TRUE );

    if ( ! empty ( $prev ) )
    {
        foreach ( $prev as $post_id )
            delete_post_meta( $post_id, '_fast_next_post' );
    }

    $next = (array) get_post_meta( $post->ID, '_fast_next_post', TRUE );

    if ( ! empty ( $next ) )
    {
        foreach ( $next as $post_id )
            delete_post_meta( $post_id, '_fast_prev_post' );
    }

    delete_post_meta( $post->ID, '_fast_prev_post' );
    delete_post_meta( $post->ID, '_fast_next_post' );
}

To use these functions, add this to the theme:

do_action( 'fast_prev_post_link' );
do_action( 'fast_next_post_link' );