mysql query expansion technology tutorial

Query expansion is used to try to broaden the scope of the full-text search results returned. Consider the following situation. You want to find all comments that mention anvils. Only one comment contains the word anvils , but you also want to find all other lines that may be relevant to your search, even if they don't contain anvils .

This is also a task of query expansion. When using query expansion, MySQL performs two scans of the data and index to complete the search:

1. First, perform a basic full-text search to find all rows that match the search criteria;

2. Second, MySQL examines these matching rows and selects all useful words (we will briefly explain how MySQL determines what is useful and what is not).

3. Next, MySQL performs a full-text search again, this time not only using the original conditions, but also using all useful words.

Use query expansion to find results that may be relevant, even if they don't contain the exact words you're looking for.

Only used in MySQL version 4.1.1 or higher The query expansion feature was introduced in MySQL 4.1.1 and therefore cannot be used in previous versions.

Here is an example, first perform a simple full-text search without query expansion:

Input:

select note_text from productnotes where match(note_text) against('anvils');

Output:

Analysis: Only one line contains the word anvils, so only one line 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:

## Analysis: This time 7 rows were returned. The first line contains the word anvils and therefore has the highest rank. The second line has nothing to do with anvils , but because it contains two words from the first line ( customer and recommend ), it is also retrieved. Line 3 also contains these same two words, but they are further back and further apart in the text, so this line is also included, but with a third rank. The third line does not refer to anvils (by their product name) either.

As you can see, query expansion greatly increases the number of rows returned, but in doing so it also increases the number of rows you don't actually want.

The more rows, the better The more rows in the table (the more text in those rows), the better the results returned using query expansion.

The above is the detailed content of mysql query expansion technology tutorial. For more information, please follow other related articles on the PHP Chinese website!