5

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

3
  • 2
    The best way to improve search is to use a search application. MySQL wasn't built around search. There are several open source options: Elasticsearch, Sphinx, Solr. You'll find plugins for each of these. Each of these is basically a database built around search as opposed to relational data. The plugins will keep your search app synced with WordPress and then direct searches through the search app. Feb 12 '14 at 21:17
  • Thanks for your input, @SeamusLeahy! Do these search applications (and their related plugins) work for the admin area? I'm mainly concerned with speeding up the admin area's search functionality.
    – rinogo
    Feb 12 '14 at 21:25
  • @rinogo Please follow up on your questions. Some of them are quite good, but it seems like you abandoned them...
    – kaiser
    Oct 9 '14 at 0:12
3

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.

0

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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