Sunday, March 14, 2010

MySQL FULLTEXT

TEXT columns in a MySQL ISAM table can be searched using the MATCH keyword which is similar to LIKE.
Select Name, Description from Artists where match(Description) AGAINST ('American'); 

+------------------+-------------------------------------+
| Name             | Description                         |
|------------------+-------------------------------------+
| Ray Charles      | Unsurpassed American Musician       | 
+------------------+-------------------------------------+

Here's the Kicker
The results can be expanded with natural language capabilities by adding WITH QUERY EXPANSION to the AGAINST clause of the MATCH. The Buddy Holly record does not include the search word but because 'unsurpassed was in proximity to the searchword 'American' found in the Ray Charles record, the search results include records which have 'unsurpassed' as well.
Select Name, Description from Artists where match(Description) AGAINST ('American' WITH QUERY EXPANSION); 

+--------------------+-------------------------------------+
| Name               | Description                         |
|--------------------+-------------------------------------+
| Ray Charles        | Unsurpassed American Musician       | 
| Buddy Holly        | Buddy had unsurpassed promise.      | 
+--------------------+-------------------------------------+

Prereqs:
ALTER TABLE Artists ENGINE=MyISAM;
ALTER TABLE Artists ADD FULLTEXT (Description);