[wp-trac] [WordPress Trac] #14722: use MATCH/AGAINST instead of LIKE when querying for matching terms posts

[WordPress Trac]

#14722: use MATCH/AGAINST instead of LIKE when querying for matching terms posts
-------------------------+--------------------------------------------------
 Reporter:  pgentoo      |       Owner:             
     Type:  enhancement  |      Status:  new        
 Priority:  normal       |   Milestone:  3.1        
Component:  Performance  |     Version:  3.0.1      
 Severity:  normal       |    Keywords:  needs-patch
-------------------------+--------------------------------------------------

Comment(by pgentoo):

 scribu, here's a patch against the 3.0 branch (although untested).  Please
 give it a shot. :)

 {{{
 Index: query.php
 ===================================================================
 --- query.php   (revision 15549)
 +++ query.php   (working copy)
 @@ -1851,12 +1851,12 @@
                         $searchand = '';
                         foreach( (array) $q['search_terms'] as $term ) {
                                 $term = addslashes_gpc($term);
 -                               $search .=
 "{$searchand}(($wpdb->posts.post_title LIKE '{$n}{$term}{$n}') OR
 ($wpdb->posts.post_content LIKE '{$n}{$term}{$n}'))";
 +                               $search .= "{$searchand}
 MATCH($wpdb->posts.post_title, $wpdb->posts.post_content)
 AGAINST('{$term}')";
                                 $searchand = ' AND ';
                         }
                         $term = esc_sql($q['s']);
                         if ( empty($q['sentence']) &&
 count($q['search_terms']) > 1 && $q['search_terms'][0] != $q['s'] )
 -                               $search .= " OR ($wpdb->posts.post_title
 LIKE '{$n}{$term}{$n}') OR ($wpdb->posts.post_content LIKE
 '{$n}{$term}{$n}')";
 +                               $search .= " OR
 MATCH($wpdb->posts.post_title, $wpdb->posts.post_content)
 AGAINST('{$term}')";

                         if ( !empty($search) ) {
                                 $search = " AND ({$search}) ";
 }}}

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/14722#comment:3>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software