Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
1  
+1 for ascii table –  GhostToast May 31 '13 at 18:04
1  
copy/paste from the terminal, with the four spaces in front of each line to keep the format –  davidmh May 31 '13 at 18:05
1  
You must be using the $in_same_cat argument, right? Or you wouldn't have, or need, the JOIN. –  s_ha_dum May 31 '13 at 18:35
    
You are right, including the category does increase the execution time. But I still need to show the previous/next for the same category, I'll edit the question with your observation. Thanks –  davidmh May 31 '13 at 18:56

1 Answer 1

up vote 4 down vote accepted

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:  http://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' );
share|improve this answer
    
Amazing, this is the exact solution. Thank you. –  davidmh Jun 1 '13 at 4:37
    
I changed it a bit, it was saving the entire post in the meta, and I added the functions get_fast_previous_post and get_fast_next_post. gist.github.com/davidmh/5689418 –  davidmh Jun 1 '13 at 5:55
    
@davidmh I made an update to store just the ID, and I improved the signature of get_fast_adjacent_post(). It would be nice to see the performance difference from your setup in hard numbers. :) –  toscho Jun 1 '13 at 8:09
2  
It's crushing, the total query time in the resquest droped from 123.3ms to 10.6ms. Tomorrow I'll be adding support for the newest and the oldest post, for its previous and next link, respectively. I'm thinking in adding the hashed position as 'endpoint', to return a null and then reset it when a new post gets published. –  davidmh Jun 1 '13 at 9:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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