Databases Reference
In-Depth Information
combinations of columns to compute the desired ranking. Suppose we want the
title
column to be more important. We can add another index on this column, as
follows:
mysql>
ALTER TABLE film_text ADD FULLTEXT KEY(title) ;
Now we can make the title twice as important for purposes of ranking:
mysql>
SELECT film_id, RIGHT(description, 25),
->
ROUND(MATCH(title, description) AGAINST('factory casualties'), 3)
->
AS full_rel,
->
ROUND(MATCH(title) AGAINST('factory casualties'), 3) AS title_rel
->
FROM sakila.film_text
->
WHERE MATCH(title, description) AGAINST('factory casualties')
->
ORDER BY (2 * MATCH(title) AGAINST('factory casualties'))
->
+ MATCH(title, description) AGAINST('factory casualties') DESC;
+---------+---------------------------+----------+-----------+
| film_id | RIGHT(description, 25) | full_rel | title_rel |
+---------+-------------- ------------+----------+-----------+
| 831 | a Car in A Baloon Factory | 8.469 | 5.676 |
| 126 | Face a Boy in A Monastery | 5.262 | 5.676 |
| 299 | jack in The Sahara Desert | 3.056 | 6.751 |
| 193 | a Composer in The Outback | 5.207 | 5.676 |
| 369 | d Cow in A Baloon Factory | 3.152 | 0.000 |
| 451 | a Dog in A Baloon Factory | 3.152 | 0.000 |
| 595 | a Cat in A Baloon Factory | 3.152 | 0.000 |
| 649 | nizer in A Baloon Factory | 3.152 | 0.000 |
However, this is usually an inefficient approach because it causes filesorts.
Boolean Full-Text Searches
In Boolean searches, the query itself specifies the relative relevance of each word in a
match. Boolean searches use the stopword list to filter out noise words, but the re-
quirement that search terms be longer than
ft_min_word_len
characters and shorter
than
ft_max_word_len
characters is disabled.
11
The results are unsorted.
When constructing a Boolean search query, you can use prefixes to modify the relative
ranking of each keyword in the search string. The most commonly used modifiers are
shown in
Table 7-3
.
Table 7-3. Common modifiers for Boolean full-text searches
Example
Meaning
dinosaur
Rows containing “dinosaur” rank higher.
Rows containing “dinosaur” rank lower.
~dinosaur
+dinosaur
Rows
must
contain “dinosaur”.
11. Full-text indexes won't even contain words that are too short or too long, but that's a different matter.
Here we refer to the fact that the server won't strip words from the search phrase if they're too short or
too long, which it normally does as part of the query optimization process.
