Is there a more efficient admin search function/plugin?

1 Views

Writers on our large WordPress installation love to use the the content searching functionality. As convenient as this functionality is, the complexity of its queries slows down our database considerably. Here’s an example of an SQL query I found in our slow query log from just a few minutes ago: 

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

Check out the size of that guy! It’s no wonder it runs slowly.

Read More

I’d be interested to hear ideas on how to code up some tweaks to this functionality to make it be a bit more efficient. (I already have a few ideas of my own; hopefully yours are better than mine! 🙂 )

Post Views: 1

Related posts

2 comments

  1. Altering the WHERE clause

    There’s a filter named posts_search that allows to filter the SQL WHERE clause used for the query during search (when WP_Query::is_search() returns true and WP_Query->s is set):

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

    return $where;
}

    Custom ORDERBY

    To intercept the ORDERBY statement (for e.g. to sort by author, so the author who searches gets his posts first/last), you can make use of 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;
}

    Fine grained SQL

    You can as well alter the posts_clauses or pre_get_posts to return even more fine tuned results by checking inside your callback if is_admin() and $query->is_search() are TRUE.

    Don’t search everything

    To exclude common terms that won’t help, you can use WP_Query::get_search_stopwords() – or better: A callback on the filter. Currently the stopwords are:

    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

    An example filter callback: 

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

}

    Hint: It looks like something (a plugin probably) is already intercepting your callback as there’re words in there that shouldn’t get searched.

  2. I would recommend changing your query to with your $args being something like this

        $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);

    There is also I more detailed description here. The only thing I can’t seem to find is to query for multiple keywords. Maybe if you do something like this it could work

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

Comments are closed.