Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.6Mb
PDF (A4) - 44.6Mb
PDF (RPM) - 40.3Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 204.6Kb
Man Pages (Zip) - 311.5Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  Functions and Operators  /  Full-Text Search Functions

12.9 Full-Text Search Functions

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

MySQL has support for full-text indexing and searching:

  • A full-text index in MySQL is an index of type FULLTEXT.

  • Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

  • MySQL provides a built-in full-text ngram parser that supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese. Parsing differences are outlined in Section 12.9.8, “ngram Full-Text Parser”, and Section 12.9.9, “MeCab Full-Text Parser Plugin”.

  • A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.

  • For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

There are three types of full-text searches:

  • A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators, with the exception of double quote (") characters. The stopword list applies. For more information about stopword lists, see Section 12.9.4, “Full-Text Stopwords”.

    Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given. For more information, see Section 12.9.1, “Natural Language Full-Text Searches”.

  • A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Certain common words (stopwords) are omitted from the search index and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a boolean search. For more information, see Section 12.9.2, “Boolean Full-Text Searches”.

  • A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 12.9.3, “Full-Text Searches with Query Expansion”.

For information about FULLTEXT query performance, see Section 8.3.5, “Column Indexes”.

For more information about InnoDB FULLTEXT indexes, see Section 15.6.2.4, “InnoDB FULLTEXT Indexes”.

Constraints on full-text searching are listed in Section 12.9.5, “Full-Text Restrictions”.

The myisam_ftdump utility dumps the contents of a MyISAM full-text index. This may be helpful for debugging full-text queries. See Section 4.6.3, “myisam_ftdump — Display Full-Text Index information”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Carlos Dias on August 8, 2011
Basically this approach makes me think twice because of the next logical steps:
1- If your working in one table with a lot of records...each time the records are updated or new lines inserted the index must be (obviously)recreated ... if it's myisam ... @writing operations the table is locked...
2- I guess that the best approach towards this it's probably the logic of: when tables are huge ... not creating indexes for text search ... create cache@sql ...(cache@sql is one index!) ...

Somehow anticipating these problems ... like i write are not problems to ignore...

Why this is the best option... because if people use one file to log the last writing operations and compare it with the file that contains the results @cache(best approach ... cronjob!) ... it's only necessary to point to the file that contains the results@cache...

The logic of:If there are 500 000 000 of conjugations of words/phrases,etc what's the need of indexing everything if only 50 000 conjugations are used/seeked,etc ...

Regards,
Carlos


  Posted by Bradley Smith on February 21, 2012
Alan, instead of creating 80 different tables, one for each category, why not partition the table by the category so the records with that category would be grouped together within the partition and then your only searching within the specific category and more direct and faster route to the data you want to search?
  Posted by Nasser W on September 2, 2013
MySQL fulltext search works well for Arabic. Just make sure of the following where needed:

1. `COLLATION = utf8_unicode_ci` & `CHARACTER SET = utf8`. (Databases, Tables, and Columns).
2. Index words of 3 letters and more. This is Very Important for Arabic, `ft_min_word_len = 3` (see `show variables like "ft_%";`)
3. Check the version of MySQL (5.5 or 5.6), and Engine (InnoDb or MyIsam)
Sign Up Login You must be logged in to post a comment.