MySQL Create Full-text indexing learn notes

Using indexes is one of the essential skills for database performance optimization. In the MySQL database, there are four kinds of indexes: Clustered index (primary key index), normal index, unique index, and the Full-text Index (fulltext Index) we will introduce here.

Full-text indexing (also known as Full-text search) is a key technology used by SEO search engines at present. It can use the "Word segmentation technology" and other algorithms to intelligently analyze the text of the key words in the frequency and importance, and then according to a certain algorithm rules intelligently filter out the search results we want. Here, we don't get to the bottom of the implementation principle, now let's look at how to create and use Full-text indexing in MySQL.

Full-text indexing can only create words less than 3 characters in the MyISAM datasheet will not be included in the Full-text index, modify the options by modifying MY.CNF

Ft_min_word_len=3

Restart the MySQL server with

Repair Table TableName Quick to regenerate the Full-text index for the data table
SELECT * FROM tablename

where match (Column1,column2) against (' Word1 word2 word3 ') >0.001
Match ... against a data record that contains at least one of the three words in the COLUMN1,COLUMN2 data column, and the data column after the match must be the same as the data column that created the Full-text index. Search terms are case-insensitive and sequential, and words less than 3 characters are often ignored. Match ... against ... The expression returns a floating-point number as the result of its own evaluation, which reflects the degree to which the result record matches the retrieved word. If no records are matched, or too many of the resulting records are matched, the expression returns 0, and the expression >0.001 to exclude the result record of Match's return value too small.

The code is as follows	Copy Code
Select *,match (column1,column2) against (' Word1 word2 word3 ') as Mtch From TableName Having mtch>0.01 ORDER BY Mtch Desc Limit 5

Find the 5 most matched records and not use Kana in the WHERE clause, so use the having

Create a Full-text cable

In MySQL, creating a Full-text index is relatively straightforward. For example, we have an article table (article) with a primary key ID (ID), an article title (title), and three fields of article content. Now we want to be able to create a Full-text index on the title and content two columns, and the article table and the Full-text index create the SQL statements as follows:

The code is as follows	Copy Code
--Create a article table CREATE TABLE article ( ID INT UNSIGNED auto_increment not NULL PRIMARY KEY, Title VARCHAR (200), Content TEXT, Fulltext (title, content)--Create a Full-text index on the title and content columns );

Above is an example of a SQL that establishes a FULL-TEXT index while creating a table. Also, if we want to create a Full-text index for a specified field in a table that already exists, as an example of a article table, we can use the following SQL statement to create:

The code is as follows	Copy Code
--Create a Full-text index for the title and content fields of an existing article table --index name is fulltext_article ALTER TABLE article ADD fulltext INDEX fulltext_article (title, content)

Once you have created a full-text index in MySQL, you should now know how to use it. It is well known that a fuzzy query in a database uses the LIKE keyword for querying, for example:

The code is as follows	Copy Code
SELECT * FROM article WHERE content like '% query string% '

So, is this how we use Full-text indexing? Of course not, we have to use a unique syntax to query using Full-text indexing. For example, we want to retrieve the specified query string Full-text in the title and content columns of the article table, and you can write SQL statements as follows:

The code is as follows	Copy Code
SELECT * from article WHERE MATCH (title, content) against (' query string ')

Note: MySQL's self-contained full-text index can only be used for data tables with database Engine MyISAM, and Full-text indexing will not take effect if it is a different data engine. In addition, MySQL's self-contained full-text index can only be full-text searchable in English and cannot be full-text searched in Chinese at present. If you need Full-text search of text data including Chinese, we need to use Sphinx (Sphinx)/coreseek technology to handle Chinese. This site will be in the following articles on Sphinx and Coreseek Introduction
。
Note 1: Currently, when using MySQL's self-contained full-text index, the desired search results will not be available if the length of the query string is too short. The default minimum length of a word that a MySQL Full-text index can find is 4 characters. In addition, if the query's string contains a stop word, the stop Word will be ignored.

NOTE 2: If possible, try creating a table and inserting all the data before you create a Full-text index, rather than creating a Full-text index directly when you create the table, because the former is more efficient than the latter's full-text indexing.