Full-text search of MySQL learning

1. What is full-text search

When using full-text search,MySQL does not need to view each row separately, and does not need to parse and process each word separately. MySQL creates an index of the words in the specified column , and the search can be made for those words. In this way, MySQL can quickly and efficiently determine which words match (which lines contain them), which words do not match, how often they match, and so on.

(Not all engines support full-text search as described in this book.) The two most commonly used engines are MyISAM and InnoDB, which support full-text search and are not supported by the latter. ）

2. Preparation of full-text search

You typically enable full-text search when you create a table. The CREATE table statement (described in the 21st chapter) accepts the FULLTEXT clause, which gives a comma-delimited list of indexed columns.

Information added when building a table: Fulltext KEY (Note_text)

MySQL indexes it according to the instructions of clause fulltext (NOTE_TEXT). Here the fulltext indexes a single column, and you can specify multiple columns if you want. After the definition, MySQL automatically maintains the index. The index is automatically updated when rows are added, updated, or deleted. You can specify fulltext when you create a table, or specify later (in which case all existing data must be indexed immediately).

3. Full-Text Search

Performs a full-text search using the two function match () and against (), where match () specifies the column being searched, and against () specifies the search expression to use.

SELECT Note_text  from  WHERE MATCH (note_text) against ('rabbit')

Note: The value passed to match () must be the same as in the Fulltext () definition. If more than one column is specified, they must be listed (and in the correct order).

Full-text searches are not case-sensitive unless you use binary mode.

Similar to the following:

SELECT Note_text  from  WHERElike%rabbit%

But an important part of full-text search is to sort the results (the rank is calculated by MySQL based on the number of rows morphemes, the number of unique words, the total number of morphemes for the entire index, and the number of rows that contain the word. ）。 Rows with higher levels are returned first,

And like is not, and the query performance is poor.

4. Using extended Query

All notes referring to anvils. Only one comment contains the word anvils, but you also want to find all the other lines that might be relevant to your search, even if they do not contain the word anvils.

This is also a task for query extensions. When using the query extension, MySQL scans the data and index two times to complete the search:

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

? Second, MySQL examines these matching rows and selects all the useful words.

? Second, MySQL is again full-text search, this time not only using the original conditions, but also use all the useful words.

SELECT Note_text  from productnotes; WHERE MATCH (Note_text) against ('rabbit' with QUERY EXPANSION);

5, Boolean query (not high efficiency and can be used in non-specified fulltext)

? the words to match;

? The word to repel (if a line contains the word, the row is not returned, even if it contains other specified words);

? Arrange hints (specify that certain words are more important than others, and that more important words are of higher rank);

? Expression grouping;
? Some other content.

Attention:

? When indexing full-text data, short words are ignored and excluded from the index . Short words are defined as words that have 3 or 3 characters (this number can be changed if needed).

? MySQL has a list of built-in non-use words (Stopword) that are always ignored when indexing full-text data. If necessary, you can override this list

? Many words appear at a high frequency, and searching for them is useless (returning too many results). Therefore, MySQL prescribes a 50% rule that if a word appears in more than 50% rows, it is ignored as a non-word . 50% rules are not used in Booleanmode.

? If the number of rows in the table is less than 3 rows , the full-text search does not return a result (because each word either does not appear or at least appears in the 50% row).

? Ignores single quotes in the word. For example, the Don ' t index is dont.

? Languages that do not have word separators, including Japanese and Chinese, do not properly return full-text search results .

Full-text search of MySQL learning