Databases Reference
In-Depth Information
mysql>
SHOW INDEX FROM sakila.film_text;
+-----------+-----------------------+-------------+------------+
| Table | Key_name | Column_name | Index_type |
+-----------+-----------------------+-------------+------------+
| ...
| film_text | idx_title_description | title | FULLTEXT |
| film_text | idx_title_description | description | FULLTEXT |
+-----------+-----------------------+-------------+------------+
Here's an example natural-language full-text search query:
mysql>
SELECT film_id, title, RIGHT(description, 25),
->
MATCH(title, description) AGAINST('factory casualties') AS relevance
->
FROM sakila.film_text
->
WHERE MATCH(title, description) AGAINST('factory casualties');
+---------+-----------------------+---------------------------+-----------------+
| film_id | title | RIGHT(description, 25) | relevance |
+---------+-----------------------+---------------------------+-----------------+
| 831 | SPIRITED CASUALTIES | a Car in A Baloon Factory | 8.4692449569702 |
| 126 | CASUALTIES ENCINO | Face a Boy in A Monastery | 5.2615661621094 |
| 193 | CROSSROADS CASUALTIES | a Composer in The Outback | 5.2072987556458 |
| 369 | GOODFELLAS SALUTE | d Cow in A Baloon Factory | 3.1522686481476 |
| 451 | IGBY MAKER | a Dog in A Baloon Factory | 3.1522686481476 |
MySQL performed the full-text search by breaking the search string into words and
matching each of them against the
title
and
description
fields, which are combined
in the full-text collection upon which the index is built. Notice that only one of the
results contains both words, and that the three results that contain “casualties” (there
are only three in the entire table) are listed first. That's because the index sorts the
results by decreasing relevance.
Unlike with normal queries, the results of full-text searches are auto-
matically ordered by relevance. MySQL cannot use an index for sorting
when you perform a full-text search. Therefore, you shouldn't specify
an
ORDER BY
clause if you want to avoid a filesort.
The
MATCH()
function actually returns the relevance as a floating-point number, as you
can see from our example. You can use this to filter by relevance or to present the
relevance in a user interface. There is no extra overhead from specifying the
MATCH()
function twice; MySQL recognizes they are the same and does the operation only once.
However, if you put the
MATCH()
function in an
ORDER BY
clause, MySQL will use a
filesort to order the results.
You have to specify the columns in the
MATCH()
clause exactly as they're specified in a
full-text index, or MySQL can't use the index. This is because the index doesn't record
in which column a keyword appeared.
This also means you can't use a full-text search to specify that a keyword should appear
in a particular column of the index, as we mentioned previously. However, there's a
workaround: you can do custom sorting with several full-text indexes on different
