Database Reference
In-Depth Information
| Customer complaint: rabbit has been able to detect trap, | 1.6408053837485|
| food apparently less effective now. | |
| Shipped unassembled, requires common tools (including | 0 |
| oversized hammer). | |
| Customer complaint: Circular hole in safe floor can | 0 |
| apparently be easily cut with handsaw. | |
| Customer complaint: Not heavy enough to generate flying | 0 |
| stars around head of victim. If being purchased for | |
| dropping, recommend ANV02 or ANV03 instead. | |
| Call from individual trapped in safe plummeting to the | 0 |
| ground, suggests an escape hatch be added. Comment | |
| forwarded to vendor. | |
+----------------------------------------------------------+----------------+
▼
Analysis
Here
Match()
and
Against()
are used in the
SELECT
instead of the
WHERE
clause. This causes all rows to be returned (as there is no
WHERE
clause).
Match()
and
Against()
are used to create a calculated column (with the
alias
rank
), which contains the ranking value calculated by the full-text search.
The ranking is calculated by MariaDB based on the number of words in the
row, the number of unique words, the total number of words in the entire
index, and the number of rows that contain the word. As you can see, the rows
that do not contain the word
rabbit
have a rank of
0
(and were therefore not
selected by the
WHERE
clause in the previous example). The two rows that do
contain the word
rabbit
each have a rank value, and the one with the word
earlier in the text has a higher rank value than the one in which the word
appeared later. This helps demonstrate how full-text searching eliminates rows
(those with a rank of
0
), and how it sorts results (by rank in descending order).
Note
Ranking Multiple Search Terms If multiple search terms are specified, those that con-
tain the most matching words will be ranked higher than those with less (or just a single
match).
As you can see, full-text searching offers functionality not available with simple
LIKE
searches. And as data is indexed, full-text searches are considerably
faster, too.
Query expansion is used to try to widen the range of returned full-text search
results. Consider the following scenario. You want to find all notes with refer-
ences to
anvils
in them. Only one note contains the word
anvils
, but you
