A brief tutorial on MySQL full-text index application

This article introduces the basic knowledge of MySQL full-text index from the following aspects:

1. Several notes on MySQL full-text index

2. The syntax of full-text index

3. An introduction to several search types

4. Examples of several search types

Several notes on full-text index

1. The search must be on an index column of type fulltext, and the column specified in match must have been specified in fulltext

2. Only Applied to tables whose table engine is MyIsam type (MySQL 5.6 and later can also be used in Innodb table engine)

3. Full-text indexes can only be created on columns of char, varchar, and text types

4. Like ordinary indexes Similarly, you can specify it when defining the table, or you can add or modify it after creating the table. 5. For a large-scale record insertion, inserting data into a table without an index and then creating an index is faster than inserting into a data table with an index. The process is much faster

6. The search string must be a constant string, not the column name of the table

7. When the selectivity of the search record exceeds 50%, it is considered that there is no match (limited only in natural search )

Full-text index search syntax

MATCH (列名1, 列名2,…) AGAINST (搜索字符串 [搜索修饰符])

The column names 1, 2, etc. specified in match are the column names specified in establishing the full-text index. The following search modifiers are explained as follows:

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 actually illustrate 3 full-text search types

IN NATURAL LANGUAGE MODE

Introduction: the default search form (without any search modifiers or the modifier is IN NATURAL LANGUAGE MODE)

Features:

The characters in the search string are all parsed into normal characters, with no special meaning

Filter the strings in the masked character list

When the selectivity of the record exceeds 50% of the time is usually considered a mismatch.

The returned records are sorted and displayed according to their relevance

IN BOOLEAN MODE

Introduction: Boolean mode search (when the search modifier is IN BOOLEAN MODE)

Features:

will parse the search string according to certain rules The meanings of special characters are subject to some logical meaning rules. For example: a certain word must appear or cannot appear, etc.

The records returned by this type of search are not sorted according to relevance

WITH QUERY EXPANSION

Introduction: A slightly more complex search form, which actually performs 2 natural searches, and can return records directly. Record of sexual relations, modifier IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier

Features: This type of search actually provides an indirect search function. For example: I search for a certain word, and the first line returned does not contain the search word. any string in . A second match can be performed based on the record words of the first search result, so that it is possible to find matching records with some indirect relationships.

Examples of several search types

Application in NATURAL LANGUAGE MODE mode:

It is still applied in the product table, where we have established a full-text index in the name field, because I need to match the relevant keywords in the name column Record the

Sql statement as follows:

SELECT * FROM product WHERE match(name) against(‘auto')

The time is not bad, more than 10,000 records were hit in nearly 870,000 records, it took 1.15 seconds, the effect is still good

Note: By default, it is based on correlation Records are returned from high to low

We can SELECT match(name) against('auto') FROM product to view the correlation value of the record, the values ​​are between 0 and 1. 0 means the record does not match

Several important features:

1. Which words will be ignored

The search term is too short. The default full-text index considers words with more than 4 characters as valid words. We can modify ft_min_word_len in the configuration. Configure

2. How to perform word segmentation

The full-text index considers a continuous valid character (the character set matched by w in the regular expression) to be a word, which can also contain a "'". But two consecutive ''s will be considered as a separator. Other delimiters such as: spaces, commas, periods, etc.

Application in BOOLEAN MODE mode:

In Boolean matching mode, we can add some special symbols to increase some logical functions of the search process. Such as the examples provided on the official website (search for strings containing mysql and (Excluding Yousql statements):

SELECT * FROM articles WHERE MATCH (title,body)
-> AGAINST (‘+MySQL -YourSQL' IN BOOLEAN MODE);

It can be seen that we have more control over search, and it looks more "high-end".

In fact, the above operation implies several meanings:

Plus sign: equivalent to and

No: equivalent to or

Let’s take a look at several important features of Boolean type search:

1. 没有50%记录选择性的限制，即使搜索结果记录超过总数的50%也同样返回结果
2. 不会自动的按记录的相关性进行降序排序
3. 可以直接应用在没有创建fulltext的全文索引上，但是这样会查询的非常慢，所以说还是别用了。
4. 支持最小、最大单词长度
5. 应用屏蔽词列表

布尔搜索支持的操作符：

n 加号 +：指示修饰的单词必须出现在记录中
n 减号 -：指示修饰的单词必须不能出现在记录中
n 没有任何操作符：单词可有可无，但是包含该词的记录相关性高
n 双引号 “ ： 将一个词组作为一个匹配。如：”one word” 匹配one word在一起的单词

下面是官方的一些实例：

至少包含一个词的记录
‘apple banana'
   
必须包含着两个词
‘+apple +juice'
   
必须包含apple，包含macintosh的记录相关性高，也可以不包含
‘+apple macintosh'
   
必须包含apple且不能喊有macintosh
‘+apple -macintosh'
   
查找apple开头单词的记录
‘apple*'
   
完整匹配some words单词
‘”some words”‘

了解了基本的mysql全文索引知识，觉得它的全文索引比like当然是强了很多。但是面对高级的搜索还是略显简陋，且性能问题也是担忧。

以上就是MySQL全文索引应用简明教程的内容，更多相关文章请关注PHP中文网（m.sbmmt.com）！