Advanced MySQL Features - High Performance MySQL - page 309

Example Meaning

-dinosaur Rows must not contain “dinosaur”.

dino* Rows containing words that begin with “dino” rank higher.

You can also use other operators, such as parentheses for grouping. You can construct
complex searches in this way.
As an example, let's again search the sakila.film_text table for films that contain both
“factory” and “casualties.” A natural-language search returns results that match either
or both of these terms, as we saw before. If we use a Boolean search, however, we can
insist that both must appear:
mysql> SELECT film_id, title, RIGHT(description, 25)
-> FROM sakila.film_text
-> WHERE MATCH(title, description)
-> AGAINST('+factory +casualties' IN BOOLEAN MODE);
+---------+---------------------+---------------------------+
| film_id | title | RIGHT(description, 25) |
+---------+---------------------+---------------------------+
| 831 | SPIRITED CASUALTIES | a Car in A Baloon Factory |
+---------+---------------------+---------------------------+

You can also do a phrase search by quoting multiple words, which requires them to
appear exactly as specified:
mysql> SELECT film_id, title, RIGHT(description, 25)
-> FROM sakila.film_text
-> WHERE MATCH(title, description)
-> AGAINST('"spirited casualties"' IN BOOLEAN MODE);
+---------+---------------------+---------------------------+
| film_id | title | RIGHT(description, 25) |
+---------+---------------------+---------------------------+
| 831 | SPIRITED CASUALTIES | a Car in A Baloon Factory |
+---------+---------------------+---------------------------+

Phrase searches tend to be quite slow. The full-text index alone can't answer a query
like this one, because it doesn't record where words are located relative to each other
in the original full-text collection. Consequently, the server actually has to look inside
the rows to do a phrase search.
To execute such a search, the server will find all documents that contain both “spirited”
and “casualties.” It will then fetch the rows from which the documents were built, and
check for the exact phrase in the collection. Because it uses the full-text index to find
the initial list of documents that match, you might think this will be very fast—much
faster than an equivalent LIKE operation. In fact, it is very fast, as long as the words in
the phrase aren't common and not many results are returned from the full-text index
to the Boolean matcher. If the words in the phrase are common, LIKE can actually be
much faster, because it fetches rows sequentially instead of in quasirandom index order,
and it doesn't need to read a full-text index.