MySQL full indexed text search - just like a real database!!

2009-03-24

• As of My Sql 3.something you can now add text indexes to your columns and do full text searches with relevance ranking! text indexes are fantastic and allow more efficient searching and categorisation of text. The relevance ranking feature is rather good too and gives you a much better idea of how your search performed. 
•  
• Issues to note are, this example uses relevance ranking as the criteria for returning results, if you take that condition out you will return every result in the database with a relevance score of 0. 
•  
• The default MySQL settings do not bother to index search terms of less than 4 characters therefore searching for anything less will bring back no results. This can be altered by setting "set ft_min_word_len =1" in your mysql config. This will result in a performance hit.  
•  
• Text indexes in large data sets can be quite processor intensive so you might also consider limiting your result set. 
•  
• ALTER TABLE `database`.`table_name` 
• ENGINE = MyISAM; 
•  
• ALTER TABLE table_name 
• MODIFY column_name TEXT; 
•  
• ALTER TABLE table_name 
• ADD FULLTEXT(column_name); 
•  
• SELECT *, 
• MATCH(column_name) 
• AGAINST ('search term') 
• AS score 
• FROM table_name 
• HAVING score > 0 
• ORDER BY score DESC;