• Choose a category
• All
• Classic-Fiction

By Root 485 0

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 contain 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.

Using Query Expansion

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 references to anvils in them. Only one note contains the word anvils, but you also want any other rows that may be related to your search, even if the specific word anvils is not contained within them.

This is a job for query expansion. When query expansion is used, MariaDB makes two passes through the data and indexes to perform your search:

• First, a basic full-text search is performed to find all rows that match the search criteria.

• Next, MariaDB examines those matched rows and selects all useful words (we explain how MariaDB figures out what is useful and what is not shortly).

• Then, MariaDB performs the full-text search again, this time using not just the original criteria, but also all the useful words.

Using query expansion you can therefore find results that might be relevant, even if they don’t contain the exact words for which you were looking.

Here is an example. First, a simple full-text search, without query expansion:

Input

SELECT note_text

FROM productnotes

WHERE Match(note_text) Against('anvils');

Output

+---------------------------------------------------------------------------+

| note_text |

+---------------------------------------------------------------------------+

| Multiple customer returns, anvils failing to drop fast enough or falling |

| backwards on purchaser. Recommend that customer considers using heavier |

| anvils. |

+---------------------------------------------------------------------------+

Analysis

Only one row contains the word anvils, so only one row is returned.

Here is the same search, this time using query expansion:

Input

SELECT note_text

FROM productnotes

WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

Output

+---------------------------------------------------------------------------+

| note_text |

+---------------------------------------------------------------------------+

| Multiple customer returns, anvils failing to drop fast enough or falling |

| backwards on purchaser. Recommend that customer considers using heavier |

| anvils. |

| Customer complaint: Sticks not individually wrapped, too easy to |

| mistakenly detonate all at once. Recommend individual wrapping. |

| Customer complaint: Not heavy enough to generate flying stars around head |

| of victim. If being purchased for dropping, recommend ANV02 or ANV03 |

| instead. |

| Please note that no returns will be accepted if safe opened using |

| explosives. |

| Customer complaint: rabbit has been able to detect trap, food apparently |

| less effective now. |

| Customer complaint: Circular hole in safe floor can apparently be easily |

| cut with handsaw. |

| Matches not included, recommend purchase of matches or detonator (item |

| DTNTR). |

+---------------------------------------------------------------------------+