For natural-language full-text searches, the columns named in the MATCH()  function must be the same columns included in some FULLTEXT  index in your table. For the preceding query, note that the columns named in the MATCH()  function ( title  and body ) are the same as those named in the definition of the article  table’s FULLTEXT  index. To search the title  or body  separately, you would create separate FULLTEXT  indexes for each column. 

You can also perform a boolean search or a search with query expansion. These search types are described in Section 12.10.2, “Boolean Full-Text Searches” , and Section 12.10.3, “Full-Text Searches with Query Expansion” . 

A full-text search that uses an index can name columns only from a single table in the MATCH()  clause because an index cannot span multiple tables. For MyISAM  tables, a boolean search can be done in the absence of an index (albeit more slowly), in which case it is possible to name columns from multiple tables. 

The preceding example is a basic illustration that shows how to use the MATCH()  function where rows are returned in order of decreasing relevance. The next example shows how to retrieve the relevance values explicitly. Returned rows are not ordered because the SELECT  statement includes neither WHERE nor ORDER BY  clauses: 

mysql> SELECT id, MATCH (title,body) AGAINST (’Tutorial’ IN NATURAL LANGUAGE MODE) AS score FROM articles; +----+---------------------+ | id | score | +----+---------------------+ | 1 | 0.22764469683170319 | | 2 | 0 | | 3 | 0.22764469683170319 | | 4 | 0 | | 5 | 0 | | 6 | 0 | +----+---------------------+ 6 rows in set (0.00 sec) 

The following example is more complex. The query returns the relevance values and it also sorts the rows in order of decreasing relevance. To achieve this result, specify MATCH()  twice: once in the SELECT  list and once in the WHERE  clause. This causes no additional overhead, because the MySQL optimizer notices that the two MATCH()  calls are identical and invokes the full-text search code only once. 

mysql> SELECT id, body, MATCH (title,body) AGAINST (’Security implications of running MySQL as root’ IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH (title,body) AGAINST (’Security implications of running MySQL as root’ IN NATURAL LANGUAGE MODE); +----+-------------------------------------+-----------------+ | id | body | score | +----+-------------------------------------+-----------------+ | 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 | | 6 | When configured properly, MySQL ... | 1.3114095926285 | +----+-------------------------------------+-----------------+ 2 rows in set (0.00 sec) 

A phrase that is enclosed within double quote ( " ) characters matches only rows that contain the phrase literally, as it was typed . The full-text engine splits the phrase into words and performs a search in the FULLTEXT  index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase"  matches "test, phrase" . If the phrase contains no words that are in the index, the result is empty. For example, if all words are either stopwords or shorter than the minimum length of indexed words, the result is empty. 

