非常大的数据库上的GET_ANNEXT_POST替代

时间:2013-05-31 作者:davidmh

我正在使用get_next_postget_previous_post (来自同一类别)在我的单个模板中显示缩略图和到尊敬的帖子的链接,但这是在一个非常大的数据库中,这给我的服务器带来了困难。

在已发布的帖子、草稿和附件之间,该网站共有49984篇帖子:

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)
我能理解为什么get_previous_post 毕竟,运行需要大量资源,它必须按日期比较所有匹配的帖子,以确定下一个或上一个:

从…起$wdbp->queries, 只是为了获取上一篇帖子:

查询:

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
发件人:

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

111.7ms
我知道这样做通常并不难,但对我来说确实如此。

有没有其他方法可以获得上一篇和下一篇文章?

Edit:

s_ha_dum 指出,包括条件$in_same_cat 这是问题的根源,通过与wp_term_relationshipswp_term_taxonomy. 解决方案是不搜索同一类别的帖子,不幸的是,我确实需要使用这个条件。

解决方案编辑:

以下是toscho\'s solution 加上一个小补丁和两个新功能:get_fast_previous_postget_fast_next_post 获取post对象。

1 个回复
最合适的回答,由SO网友:fuxia 整理而成

我看不出有什么方法可以加快查询速度,但我们可以缓存结果。

不幸的是,没有可以绕过的钩子next_post_link()previous_post_link(), 因此,我们必须用自定义函数替换这些函数。

以下示例代码使用post meta field 存储结果。可能会有副作用——我试图覆盖大多数病例,但可能遗漏了一些内容。

<?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\' );
}
要使用这些功能,请将其添加到主题中:

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

结束

相关推荐

Performance on WPMS

我的WPMS站点托管在8核/32mb RAM服务器上,但响应时间非常长。我们有大约1000个博客(单个db上有35000多个表)和70000个页面浏览量。我认为我可以缩短响应时间,将具有更多页面浏览量的博客移动到单独的DB中,并使用hyper DB插件将所有博客拆分为每个DB 100个博客。你觉得怎么样?