MariaDB Crash Course - Ben Forta [55]
Analysis
This time seven rows were returned. The first contains the word anvils and is thus ranked highest. The second row has nothing to do with anvils, but as it contains two words that are also in the first row (customer and recommend) it was retrieved, too. The third row also contains those same two words, but they are further into the text and further apart, and so it was included, but ranked third. And this third row does indeed refer to anvils (by their product name).
As you can see, query expansion greatly increases the number of rows returned, but in doing so also increases the number of returns that you might not actually want.
* * *
Tip: The More Rows the Better
The more rows in your table (and the more text within those rows), the better the results returned when using query expansion.
* * *
Boolean Text Searches
MariaDB supports an additional form of full-text searching called boolean mode. In boolean mode you may provide specifics as to
• Words to be matched
• Words to be excluded (if a row contained this word it would not be returned, even though other specified words were matched)
• Ranking hints (specifying which words are more important than others so they can be ranked higher)
• Expression grouping
• And more
* * *
Tip: Useable Even Without a FULLTEXT Index
Boolean mode differs from the full-text search syntax used thus far in that it may be used even if no FULLTEXT index is defined. However, this would be a slow operation (and the performance would degrade further as data volume increased).
* * *
To demonstrate what IN BOOLEAN MODE does, here is a simple example:
Input
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
Output
+---------------------------------------------------------------------------+
| note_text |
+---------------------------------------------------------------------------+
| Item is extremely heavy. Designed for dropping, not recommended for use |
| with slings, ropes, pulleys, or tightropes. |
| Customer complaint: Not heavy enough to generate flying stars around head |
| of victim. If being purchased for dropping, recommend ANV02 or ANV03 |
| instead. |
+---------------------------------------------------------------------------+
Analysis
This full-text search retrieves all rows containing the word heavy (there are two of them). The keywords IN BOOLEAN MODE are specified, but no boolean operators are actually specified and so the results are just as if boolean mode had not been specified.
* * *
Note: IN BOOLEAN MODE Behaves Differently
Although the results in this example are the same as they would be without IN BOOLEAN MODE, there is an important difference in behavior (even if it did not manifest itself in this particular example). I point these out in the “Full-Text Search Usage Notes” section later in this chapter.
* * *
To match the rows that contain heavy but not any word beginning with rope, the following can be used:
Input
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy –rope*' IN BOOLEAN MODE);
Output
+---------------------------------------------------------------------------+
| note_text |
+---------------------------------------------------------------------------+
| Customer complaint: Not heavy enough to generate flying stars around head |
| of victim. If being purchased for dropping, recommend ANV02 or ANV03 |
| instead. |
+---------------------------------------------------------------------------+
Analysis
This time only one row is returned. Again, the word heavy is matched, but this time –rope* instructs MariaDB to explicitly exclude any row that contains rope* (any word beginning with rope, including ropes, which is why one of the rows was excluded).
You have now seen two full-text search boolean operators: - excludes a word and * is the truncation operator (think of it as a wildcard used at the end of a word). Table 18.1 lists all the supported boolean operators.
Table 18.1 Full-Text Boolean Operators
Here are