MySQL Forums
Forum List  »  Full-Text Search

Re: Excet mach order come first in fulltext search
Posted by: Rick James
Date: April 09, 2011 04:11PM

SELECT  p. * , m.category_id AS cid, m.category_name ,
             MATCH(p.`what`, p.`description`, p.`short_description`) AGAINST ('james Bond Good Man' IN BOOLEAN MODE)
         OR  MATCH(m.category_name)  AGAINST ('james Bond Good Man' IN BOOLEAN MODE) as score
    FROM  tbl_post AS p
    LEFT JOIN  map_category_post AS m ON p.post_id = m.post_id
    WHERE  ( MATCH(p.`what`, p.`description`, p.`short_description`)
                  AGAINST ('james Bond Good Man' IN BOOLEAN MODE)
              OR  MATCH(m.category_name) AGAINST ('james Bond Good Man' IN BOOLEAN MODE) )
      AND  p.adult_content =0
    GROUP BY  p.post_id
    order by  score desc

* don't do OR in the SELECT part and expect "score" to be useful.

* Do you know that AGAINST ('+james +Bond +Good +Man' IN BOOLEAN MODE) will require all 4 words; what you have does not.

* For "exact match", will this work?
(what = 'james Bond Good Man' OR description = 'james Bond Good Man' OR short_description = 'james Bond Good Man')

* If not, what do you mean by exact match?

* Consider UNION:
(
    SELECT ..., 999 as score ... WHERE what = 'james Bond Good Man'...
) UNION (
    SELECT ..., MATCH... as score ... WHERE MATCH ...
) ORDER BY score desc;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Excet mach order come first in fulltext search
1735
April 09, 2011 04:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.