Home / Indexes / Full Text Indexes / Question No: 170

Before to start work on full-text search, read the following main features and basic information about MySQL full-text search

Main features and basic information about full-text search

• Currently, only MyISAM (one of the table types in MySQL) supports Full-text searches.
• A FULLTEXT index can only be created for non-binary data types namely CHAR, VARCHAR and TEXT.
• FULLTEXT index can be created for a single field or combination of fields.
• The simple usage of MATCH() function is available to all MySQL Servers from version 2.23.23.
• The advance usage (the IN BOOLEAN MODE) is available from version 4.0.1.
• MySQL 4.1.1, Full-Text search supports WITH QUERY EXPANSION modifier.
• As of MySQL 4.1, the use of multiple character sets within a single table is supported.
• Any word less than or equal to 3 characters in length are not indexed by MySQL
• Any word that appear in more than 50% of the rows are not indexed. In other words, if a table contains 2 or less rows, a search on a FULLTEXT index will nothing. To get round the 50% threshold, IN BOOLEAN MODE modifier can be used
• MySQL maintains stopword list (varies version to version) based upon common English words. So the words in the stopword list are excluded from the research results. However, this list can be changed as required.  (A stopword is a word such as "the" or "some" that is so common that it is considered to have zero semantic value).
  
• FULLTEXT index supports search for more than one word at a time. You can do so by separating the words with comma.
• Hyphenated words are excluded from the search. Basically they are indexed as separated words.
• By default, the search is performed case-insensitively.
• Results are returned in order of relevance, from highest to lowest.
• MySQL only performs the FULLTEXT search once, even though the two MATCH clauses are different.