MySQL FULLTEXT Indexing and Searching
MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.
To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:
ALTER TABLE news ADD FULLTEXT(headline, story);
Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:
SELECT headline, story FROM worldcup WHERE MATCH (headline,story) AGAINST ('spain');
The result of this query is automatically sorted by relevancy.
MATCH
The MATCH function is used to specify the column names that identify your FULLTEXT collection. The column list inside the MATCH function must exactly match that of the FULLTEXT index definition, unless your search in boolean mode (see below).
AGAINST
The AGAINST function is where your full text search query goes. Besides the default natural language search mode, you can perform boolean mode searches, and use query expansion.
Boolean Mode Searches
SELECT headline, story FROM worldcup WHERE MATCH (headline,story) AGAINST ('+spain -argentina' IN BOOLEAN MODE);
The above statement would match news stories about spain but not those that mention spain argentina.
See the MySQL documentation on Boolean Mode searches for more info.
Query Expansion
The Blind Query Expansion (or automatic relevance feedback) feature can be used to expand the results of the search. This often includes much more noise, and makes for a very fuzzy search.
In most cases you would use this operation if the users query returned just a few results, you try it again WITH QUERY EXPANSION and it will add words that are commonly found with the words in the query.
SELECT headline, story FROM worldcup WHERE MATCH (headline,story) AGAINST ('spain' WITH QUERY EXPANSION);
The above query might return all news stories about spain, not just ones containing argentina.
Some points about Full-Text searching in MySQL:
For more details refer to the following sites
http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html
http://dev.mysql.com/doc/refman/5.0/en/full-text-adding-collation.html