NYCPHP Meetup

[nycphp-talk] Tuning MySQL Full Text Search

Rob Marscher rmarscher at beaffinitive.com
Wed Aug 22 12:31:00 EDT 2007


On Aug 22, 2007, at 10:50 AM, Ben Sgro ((ProjectSkyLine)) wrote:
> I'd like to tune this to have different weights for words, because  
> I'm not happy with the search results.
>         $dbObject->DatabaseQuery('SELECT id, title, body, links_to,'
>                                 . ' MATCH(title, body)'
>                                 . ' AGAINST (' . $dbObject->Safe 
> ($searchStr)
>                                 . ' IN BOOLEAN MODE)'
>                                 . ' AS score FROM ' .  
> DATABASE_TABLE_CONTENT
>                                 . ' WHERE MATCH (title, body)'
>                                 . ' AGAINST (' . $dbObject->Safe 
> ($searchStr)
>                                 . ' IN BOOLEAN MODE)'
>                                 . ' ORDER BY score DESC',
>                                 constReturnArray, LOG_LEVEL_DEBUG);

Hey Ben,

Are you not happy with the relevance sorting?  Or is it not returning  
rows that you think should match... or return too many rows?  What's  
an example of how you could weight the words?

You should test this to see if it's true... but I've seen it  
mentioned that the score returned by "IN BOOLEAN MODE" is an integer  
with the number of terms matched.  Without "IN BOOLEAN MODE", it  
gives a floating point number that " is computed based on the number  
of words in the row, the number of unique words in that row, the  
total number of words in the collection, and the number of documents  
(rows) that contain a particular word."

Actually... do you need the "IN BOOLEAN MODE"?  Otherwise, results  
are automatically sorted on relevance.

Also... you could put a higher weight on title matches over body  
matches:
( (3 * MATCH(title) AGAINST ('term')) + (1 * MATCH(body) AGAINST  
('term')) )

-Rob



More information about the talk mailing list