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

 Thinking more about this: using UNION has some drawbacks like not being
 able to use ORDER BY in the individual SELECTs unless there is LIMIT. Also
 5 SELECTs are getting slow when adding all ANDs and ORs from the standard
 query.

 Modified @scribu's patch to include the same sort conditions and use CASE
 in the ORDER BY. That is quite faster. When searching for "test post" on
 the Posts page the produced query is:

 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1
 AND (((wp_posts.post_title LIKE '%test%')
   OR (wp_posts.post_content LIKE '%test%'))
 AND ((wp_posts.post_title LIKE '%post%')
   OR (wp_posts.post_content LIKE '%post%')))
 AND wp_posts.post_type = '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
 (CASE
   WHEN wp_posts.post_title LIKE '%test post%' THEN 1
   WHEN wp_posts.post_content LIKE '%test post%' THEN 2
   WHEN wp_posts.post_title LIKE '%test%' AND wp_posts.post_title LIKE
 '%post%' THEN 3
   WHEN wp_posts.post_content LIKE '%test%' AND wp_posts.post_content LIKE
 '%post%' THEN 4
   WHEN wp_posts.post_title LIKE '%test%' OR wp_posts.post_title LIKE
 '%post%' THEN 5
   ELSE 6
 END),
 wp_posts.post_date DESC
 LIMIT 0, 20

 0.0024361610412598
 }}}

 This seems to work well and is quite fast on my test install. Would be
 good to test on a site with 300 - 400k rows in wp_posts.

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