MariaDB Crash Course - Ben Forta [53]
Full-text searches are case insensitive, unless BINARY mode (not covered in this chapter) is used.
* * *
The truth is that the search just performed could just as easily have used a LIKE clause, as seen here:
Input
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';
Output
+---------------------------------------------------------------------------+
| note_text |
+---------------------------------------------------------------------------|
| Quantity varies, sold by the sack load. All guaranteed to be bright and |
| orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently |
| less effective now. |
+---------------------------------------------------------------------------+
Analysis
This SELECT retrieves the same two rows, but the order is different (although that may not always be the case).
Neither of the two SELECT statements contained an ORDER BY clause. The latter (using LIKE) returns data in no particularly useful order. But the former (using full-text searching) returns data ordered by how well the text matched. Both rows contained the word rabbit, but the row that contained the word rabbit as the third word ranked higher than the row that contained it as the twentieth word. This is important. An important part of full-text searching is the ranking of results. Rows with a higher rank are returned first (as there is a higher degree of likelihood that those are the ones you really wanted).
To demonstrate how ranking works, look at this example:
Input
SELECT note_text,
Match(note_text) Against('rabbit') AS rank
FROM productnotes;
Output
+----------------------------------------------------------+----------------+
| note_text | rank |
+----------------------------------------------------------+----------------+
| Customer complaint: Sticks not individually wrapped, too | 0 |
| easy to mistakenly detonate all at once. Recommend | |
| individual wrapping. | |
| Can shipped full, refills not available. Need to order | 0 |
| new can if refill needed. | |
| Safe is combination locked, combination not provided | 0 |
| with safe. This is rarely a problem as safes are | |
| typically blown up or dropped by customers | |
| Quantity varies, sold by the sack load. All guaranteed | 1.5905543170914|
| to be bright and orange, and suitable for as rabbit bait.| |
| Included fuses are short and have been known to detonate | 0 |
| too quickly for some customers. Longer fuses are | |
| available (item FU1) and should be recommended. | |
| Matches not included, recommend purchase of matches or | 0 |
| detonator (item DTNTR). | |
| Please note that no returns will be accepted if safe | 0 |
| opened using explosives. | |
| Multiple customer returns, anvils failing to drop fast | 0 |
| enough or falling backwards on purchaser. Recommend | |
| that customer considers using heavier anvils. | |
| Item is extremely heavy. Designed for dropping, not | 0 |
| recommended for use with slings, ropes, pulleys, or | |
| tightropes. | |
| 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