[wp-trac] [WordPress Trac] #7394: Search: order results by relevance

[WordPress Trac]

#7394: Search: order results by relevance
-------------------------+-----------------------------
 Reporter:  markjaquith  |       Owner:
     Type:  enhancement  |      Status:  assigned
 Priority:  normal       |   Milestone:  Future Release
Component:  General      |     Version:  2.6
 Severity:  normal       |  Resolution:
 Keywords:  has-patch    |
-------------------------+-----------------------------

Comment (by azaozz):

 The ORDER BY part of the query with 7394-3.patch is:

 {{{
 ...
 ORDER BY
 (CASE
   WHEN wp_posts.post_title LIKE '%test post%' THEN 1
   WHEN wp_posts.post_title LIKE '%test%' AND wp_posts.post_title LIKE
 '%post%' THEN 2
   WHEN wp_posts.post_title LIKE '%test%' OR wp_posts.post_title LIKE
 '%post%' THEN 3
   WHEN wp_posts.post_content LIKE '%test post%' THEN 4
   ELSE 5
 END),
 wp_posts.post_date DESC
 LIMIT 0, 20

 time as reported by SAVEQUERIES: 0.0022249221801758
 }}}

 And for a single term search:

 {{{
 ...
 ORDER BY
 wp_posts.post_title LIKE '%test%' DESC,
 wp_posts.post_date DESC
 LIMIT 0, 20

 0.0019049644470215
 }}}

 Replacing the first match with regexp doesn't affect the speed much,
 however it wouldn't match plural form of the terms, etc. as @tomauger
 mentioned above. So "test posts" would not have the highest priority when
 searching for "test post":

 {{{
 ...
 ORDER BY
 (CASE
   WHEN wp_posts.post_title REGEXP '[[:<:]]test post[[:>:]]' THEN 1
   WHEN wp_posts.post_title LIKE '%test%' AND wp_posts.post_title LIKE
 '%post%' THEN 2
   WHEN wp_posts.post_title LIKE '%test%' OR wp_posts.post_title LIKE
 '%post%' THEN 3
   WHEN wp_posts.post_content LIKE '%test post%' THEN 4
   ELSE 5
 END),
 wp_posts.post_date DESC
 LIMIT 0, 20

 0.0022361278533936
 }}}

 In that terms the best speed/precision balance seems to be when using the
 "brute force" LIKE matching in ORDER BY.

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