有没有更高效的管理员搜索功能/插件?

时间:2014-02-12 作者:rinogo

我们大型Wordpress安装上的作者喜欢使用内容搜索功能。尽管此功能很方便,但其查询的复杂性大大降低了数据库的速度。以下是几分钟前我在慢速查询日志中找到的一个SQL查询示例:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
FROM wp_posts  
WHERE 1=1  
AND (
    ((wp_posts.post_title LIKE \'%Made%\') OR (wp_posts.post_content LIKE \'%Made%\')) 
    AND ((wp_posts.post_title LIKE \'%in%\') OR (wp_posts.post_content LIKE \'%in%\')) 
    AND ((wp_posts.post_title LIKE \'%the%\') OR (wp_posts.post_content LIKE \'%the%\')) 
    AND ((wp_posts.post_title LIKE \'%shade:%\') OR (wp_posts.post_content LIKE \'%shade:%\')) 
    AND ((wp_posts.post_title LIKE \'%Easy%\') OR (wp_posts.post_content LIKE \'%Easy%\')) 
    AND ((wp_posts.post_title LIKE \'%tips%\') OR (wp_posts.post_content LIKE \'%tips%\')) 
    AND ((wp_posts.post_title LIKE \'%to%\') OR (wp_posts.post_content LIKE \'%to%\')) 
    AND ((wp_posts.post_title LIKE \'%care%\') OR (wp_posts.post_content LIKE \'%care%\')) 
    AND ((wp_posts.post_title LIKE \'%for%\') OR (wp_posts.post_content LIKE \'%for%\')) 
    AND ((wp_posts.post_title LIKE \'%your%\') OR (wp_posts.post_content LIKE \'%your%\')) 
    AND ((wp_posts.post_title LIKE \'%outdoor%\') OR (wp_posts.post_content LIKE \'%outdoor%\')) 
    AND ((wp_posts.post_title LIKE \'%furniture%\') OR (wp_posts.post_content LIKE \'%furniture%\'))
)  
AND wp_posts.post_type = \'s5_post\' 
AND (
    wp_posts.post_status = \'publish\' 
    OR wp_posts.post_status = \'future\' 
    OR wp_posts.post_status = \'draft\' 
    OR wp_posts.post_status = \'pending\' 
    OR wp_posts.post_status = \'private\'
)  
ORDER BY wp_posts.post_date DESC LIMIT 0, 20
看看那家伙的身材!难怪它跑得很慢。

我很想听听关于如何对这个功能进行一些调整以提高效率的想法。(我已经有了一些自己的想法;希望你的比我的好!:)

2 个回复
SO网友:kaiser

更改WHERE 子句中有一个名为posts_search 允许筛选SQLWHERE 用于搜索期间查询的子句(当WP_Query::is_search() 返回true和WP_Query->s 已设置):

add_filter( \'posts_search\', \'wpse134392PostsSearchSQL\', 20, 2 );
function wpse134392PostsSearchSQL( $sql, $wp_query )
{
    // Alter SQL clause here

    return $where;
}

自定义ORDERBY

拦截ORDERBY 语句(例如,按作者排序,以便搜索的作者首先/最后获得其帖子),您可以使用posts_search_orderby:

add_filter( \'posts_search_orderby\', \'wpse134392PostsSearchOrderbySQL\', 20, 2 );
function wpse134392PostsSearchOrderbySQL( $orderby, $wp_query )
{
    if ( is_admin() )
        return $GLOBALS[\'wpdb\']->posts."post_date";

    return $orderby;
}
细粒度SQLposts_clausespre_get_posts 通过检查回调函数内部返回更精细的结果,如果is_admin()$query->is_search()TRUE.

不要搜索所有内容要排除没有帮助的常用术语,可以使用WP_Query::get_search_stopwords() - 或者更好:对过滤器进行回调。目前停止词有:

about,an,are,as,at,be,by,com,for,from,how,in,is,it,of,on,or,that,the,this,to,was,what,when,where,who,will,with,www
过滤器回调示例:

add_action( \'wp_search_stopwords\', \'wpse134392SearchStopwords\' );
function wpse134392SearchStopwords( $stopwords )
{
    return $stopwords + array(
        \'my\',
        \'your\',
        \'easy\',
    );

}
提示:看起来有什么东西(可能是插件)已经在拦截你的回调,因为里面有一些不应该被搜索的词。

SO网友:Kyle

我建议您将查询更改为,并将$args设置为如下所示

    $args = array(
        \'post_status\' => array(\'pending\', \'draft\', \'future\' ), 
        \'post_type\' => array( \'post\', \'page\', \'movie\', \'book\' ),
        \'orderby\' => \'date\', 
        \'order\' => \'DESC\',
        \'s\' => \'keyword\'
    );
    $search_query = new WP_Query($args);
还有更详细的描述here. 我似乎唯一找不到的就是查询多个关键字。也许如果你做这样的事情,它可以工作

\'s\' => \'keyword1+keyword2+keyword3\'

结束

相关推荐

自定义分类Meta Admin列

我添加了一个自定义的分类法-shop\\u departments,它是分层的。然后,我在这个新的分类法中添加了一个元字段-term\\u meta[首页]-这一切都很好。然而,在分类法的管理屏幕上,我需要一列元数据,这样用户可以一目了然地看到哪些部门被分配到了首页。我不知道如何在这个页面上添加列。我想做如下事情:// Register the column function front_page_column_register( $columns ) { $columns[\'front