Tuesday, 13 April 2010

What is FULLTEXT search in mysql?

FULLTEXT search is used to search the data from Mysql.

Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements.
For example:
SELECT name, title FROM news
WHERE MATCH (name,title) AGAINST ('google');


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).


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.

FULLTEXT search works only datatype varchar and text.  also, with MYISAM table type.


A few restrictions affect MySQL FULLTEXT indices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the SET command.
  • FULLTEXT indices 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 stopwords file that has a list of common words (i.e., the, that, has) which are not returned in your search. In other words, searching for the will 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.


No comments:

Post a Comment