Once you have a
FULLTEXTindex, you can search it using
SELECT name, title FROM news WHERE MATCH (name,title) AGAINST ('google');
MATCHfunction is used to specify the column names that identify your
FULLTEXTcollection. The column list inside the
MATCHfunction must exactly match that of the
FULLTEXTindex definition, unless your search in boolean mode (see below).
AGAINSTfunction 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.
FULLTEXT search works only datatype varchar and text. also, with MYISAM table type.
RestrictionsA few restrictions affect MySQL
FULLTEXTindices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the
FULLTEXTindices are NOT supported in InnoDB tables.
- MySQL requires that you have at least three rows of data in your result set before it will return any results.
- By default, if a search term appears in more than 50% of the rows then MySQL will not return any results.
- By default, your search query must be at least four characters long and may not exceed 254 characters.
- MySQL has a default
stopwordsfile that has a list of common words (i.e.,
has) which are not returned in your search. In other words, searching for
thewill return zero rows.
- According to MySQL's manual, the argument to
AGAINST()must be a constant string. In other words, you cannot search for values returned within the query.