A concise tutorial on the application of Mysql Full-text indexing _mysql

This article introduces the basics of MySQL Full-text indexing from the following aspects:

1. Several considerations for MySQL Full-text indexing
2. Syntax for Full-text indexing
3. Introduction to several search types
4. Examples of several search types

Several considerations for Full-text indexing

• Search must be on an indexed column of type fulltext, the column specified in match must be specified in fulltext
• Can only be applied to tables with a table engine of type MyISAM (MySQL 5.6 can also be used in the InnoDB table engine)
• Full-text indexing can only be created on columns of char, varchar, text type only
• Like a normal index, you can specify when you define a table, or you can add or modify a table after you create it
• For a large number of record inserts, it is much faster to create an index after inserting data into a table without an index than to insert it into an indexed datasheet
• The search string must be a constant string and cannot be a table column name
• When search records are more selective than 50%, there is no match (restricted in natural search only)

Full-text Indexing Search Syntax

MATCH (column name 1, column name 2,...) Against (search string [search modifier])

The column names 1, 2, specified in match, are the names of the columns specified in the Full-text index, and the following search modifiers are described below:

Search_modifier:

{in
NATURAL LANGUAGE MODE
| In NATURAL LANGUAGE MODE with QUERY expansion
| In BOOLEAN MODE
| With QUERY expansion
}

Introduction to several search types

The search modifiers above, in fact, illustrate the 3 types of Full-text Search

In NATURAL LANGUAGE MODE

Introduction: Default search form (without any search modifiers or modifiers as in NATURAL LANGUAGE MODE)

Characteristics:

1. No special meaning for characters in the search string that are parsed into normal characters
2. Filter strings in a masked character list
3. When the selectivity of a record exceeds 50%, it is usually considered a mismatch.
4. Returns records sorted by relevance of records

In BOOLEAN MODE

Introduction: Boolean-mode search (case of search modifiers in Boolean mode)

Characteristics:

1. Will parse the meaning of special characters in the search string according to certain rules, and make some rules of logic meaning. such as: A word must appear, or can not appear, and so on.
2. This type of search returns records that are not sorted by relevance

With QUERY expansion

Introduction: A slightly more complex form of search, which is actually done 2 times natural Search, can return records of direct introductory relationships, modifiers in NATURAL LANGUAGE MODE with query expansion or with query expansion Modifier

Feature: This type of search actually provides an indirect search function, such as: I search for a word, and the first line returned does not contain any strings in the search term. A second match can be made based on the record word for the first search result, which may find a matching record of some indirect relationship.

An introduction to several examples of search types

Application in NATURAL LANGUAGE mode:

Or is it applied to the product table where we set up the Full-text index in the name field because I need to match the relevant record in the name column according to the keyword

The SQL statement is as follows:

SELECT * from product WHERE match (name) against (' auto ')

Time is good, in nearly 87w record hit 1w Many, time-consuming 1.15 seconds, the effect is good

Note: The default is to return records based on dependencies from high to low

We can select match (name) against (' auto ') from product to view the correlation values of records, all between 0 and 1, and 0 for record mismatches

a few important features:

1. Which words will be ignored

The search term is too short the default Full-text index thinks more than 4 characters of words are valid words, we can modify Ft_min_word_len in configuration to configure
Words in the screen list the default Full-text index masks Some common words because they are too common and have no semantic effect, so the search process is negligible. Of course, this list can also be configured.
2. How to do the word segmentation

Full-text indexing considers a contiguous valid character (the regular \w matching character set) to be a word or to contain a "'", but a contiguous two ' will be considered a delimiter. Other delimiters such as: spaces, commas, periods, etc.

Applications in BOOLEAN mode:

In the Boolean matching mode, we can add some special symbols to increase the logic function of the search process. As provided in the official website (search for statements that contain MySQL strings and do not contain yousql):

SELECT * from articles WHERE MATCH (title,body)
-> against (' +mysql-yoursql ' in BOOLEAN MODE);

As we can see, we have a little more control over the search, which looks "tall".

In fact, the above operation implies several meanings:

Plus: equivalent to and
Minus sign: equivalent to not
No: equivalent to or

Here are some of the key features of the Boolean type search:

1. No 50% record selectivity restrictions, even if the search results record more than 50% of the total also return results
2. Do not automatically sort descending by record dependency
3. Can be directly applied to not create a Fulltext Full-text index, but this will be very slow to query, so say it or not.
4. Support minimum and maximum word length
5. Apply the screen word list

Boolean search-supported operators:

N Plus +: Indicates that the decorated word must appear in the record
n Minus sign-: Indicates that the decorated word must not appear in the record
n does not have any operators: the word is optional, but the record associated with the word is high
N double quotes ": match a phrase as one." such as: "One word" matches a word in one word

Here are some of the official examples:

A record of at least one word
' Apple banana '
 
must contain two words
' +apple +juice '
 
must contain Apple, the record associated with Macintosh is high, or it may not contain
' + Apple Macintosh '
 
must contain apple and cannot shout for Macintosh
' +apple-macintosh ' to
 
find the record of Apple's opening words
' apple* '
 
Complete match some words word
' some words '

Knowing the basics of MySQL full-text indexing, it feels that its full-text indexing is a lot stronger than like, of course. But in the face of advanced search is still slightly simple, and performance problems are also worrying.

I just as a primer to understand, but also to the official website some basic knowledge translation.