MyISAM support full text search. InnoDB does not.
Wildcard and regular expression matching requires that MySQL try and match each and every row in a table, and table indexes are rarely used. As such, these searches can be very time consuming as the number of rows to be searched grows.
When full-text searching is used, MySQL does not need to look at each row individually, analyzing and processing each word individually. Rather, an index of words (in specified columns) is created by MySQL, and searches can be made against those words. MySQL can thus quickly and efficiently determine which words match (which rows contains them), which don't, how often they match, and so on.
In order to perform full-text searches, the columns to be searched must be indexed and constantly re-indexed as data changes. MySQL handles all indexing and re-indexing automatically after table columns have been appropriately designated. After indexing, SELECT can be used with Match() and Against() to actually perform the searches.
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
The column name passed to Match() must be the same as the one used in FULLTEXT() definition. If multiple columns are specified, all of them must be listed, and in the correct order.
Full-text search are not case sensitive, unless BINARY mode is used.
To include related terms:
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
