Q:

SQL search result ranking

I have a table call objects which there are the columns:
object_id, 
name_english(vchar), 
name_japanese(vchar), 
name_french(vchar), 
object_description 

for each object. 
When a user perform a search, they may enter either english, japanese or french... and my sql statement is:
SELECT  
 o.object_id,  
 o.name_english,
 o.name_japanese,  
 o.name_french,  
 o.object_description  
FROM 
 objects AS o
WHERE 
 o.name_english LIKE CONCAT('%',:search,'%') OR 
 o.name_japanese LIKE CONCAT('%',:search,'%') OR 
 o.name_french LIKE CONCAT('%',:search,'%')    
ORDER BY 
 o.name_english, o.name_japanese, o.name_french ASC

And some of the entries are like: 
Tin spoon,  
Tin Foil,  
Doctor Martin Shoes,  
Martini glass,  
Cutting board,  
Ting Soda.   

So, when the user search the word "Tin" it will return all results of these, but instead I just want to return the results which specific include the term "Tin" or displaying the result and rank them by relevance order. How can I achieve that?
Thanks.

A:

You can use MySQL FULLTEXT indices to do that. This requires the MyISAM table type, an index on (name_english, name_japanese, name_french, object_description) or whatever fields you want to search on, and the appropriate use of the MATCH ... AGAINST operator on exactly that set of columns.
See the manual at http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html, and the examples on the following page http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

