Is there a more efficient admin search function/plugin?

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.

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! 🙂 )

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.