MySQL Full-Text Searching

[keyur.delhi]

Pre-Requisites:

 

• MySQL 3.23.23 or higher (for natural-language full-text searching) or MySQL 4 or higher (for Boolean full-text searching)
  
• Requires an index of type FULLTEXT on table [ ALTER TABLE <table_name> ADD FULLTEXT(<column_name>);]
   
  
• Full-text indexes can be used only with MyISAM tables
  
• Only applied on columns having following type: CHAR, VARCHAR, or TEXT
   
  
• The index doesn’t contain every word
  
• A list of ‘stopwords’ are ignored from being indexed. (Common words in English language) [ Solution: Check ft_stopword_file, can be replaced with an external file ]
   
  
• Index ignores words unless they’re longer than ft_min_word_len and shorter than ft_max_word_len characters
   
  
• A column that uses the latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches. Otherwise, case-insensitive search will be performed
  

 

 

 

Usage:

 

SELECT <field1>,<field2>,... FROM <table_name> WHERE <condition> AND MATCH(<field_name>) AGAINST(<value>)

 

 

[fenway]

Huh?