The 18th Chapter Full Text Search

1. There are several drawbacks to searching for text using like and regexp: A: Low performance, wildcard wildcards regular the expression usually requires MySQL to try to match all rows in the table, and because of the number of rows, these searches can be time consuming B: it is difficult to use regular expressions and wildcards to clarify the null value C: Although search based on wildcard wildcards regular provides very flexible efficiency, none of them can provide an intelligent selection result 2. For full-text search, the searched columns must be indexed and re-indexed as the data changes. After the index, select can be used with match () and against (). 3. You typically enable full-text search when you create a table. The CREATE TABLE statement accepts the FULLTEXT clause as follows:

Note: Instead of using fulltext when importing data, you should first import the data and then define Fulltext, which is 4 more efficient. After the index, use match () to specify the column to be searched, against () to specify the search expression to use. The value passed to match must be the same as defined in Fulltext, and if more than one column is specified, the order of the columns is also correct. An important part of the full-text search is to sort the results, with higher-level rows returning first, and the morphemes rows higher than the words.

5. Using the extended query (with query expansion): MySQL scans both sides of the data and index to complete the search, first to find all the rows that match the search criteria, followed by MySQL to check the matching rows and select all the useful words, then perform a full-text search, Not only use the original condition, but also use all the useful words

As you can see, the extended query increases the number of rows returned, but also returns some useless rows 6. Boolean Text Search (in Boolean mode) provides the following: A: Word to match B: Word to repel C: Arrange hint D: Expression Group E: Other content +: included, The word must exist-: excluded, the word must not exist;: contains, increases the rank <: Contains, decreases the rank (): The word is composed of expressions ~: Cancels the sort value of a value *: The ending Wildcard "": defines a phrase that, unlike a list of individual words, matches the entire phrase

7. Boolean Search Note: In Boolean mode, the return rows are not sorted in descending order of rank values, that is, they can be ordered in a non-Fulltext column, but not sorted by a Boolean search, but with a low efficiency of 8. Full-Text Search Description: A: When indexing full-text data, short words are excluded from the index. A short word is defined as a word with 3 words (the number can be changed) B:mysql with a list of built-in non-used words that are always ignored in the index (this table can be overwritten) C: If a word appears in the line at a frequency greater than 50, it is ignored as a non-term. This rule is not used in Boolean search in D: full-text search is supported only in the MyISAM database engine E: Not appropriate for Chinese return full-text search results f: Omit quotation marks in the word, such as the Don ' t index is dont

　　

The 18th Chapter Full Text Search