MySQL full-text index learning notes

Full-text indexing is a mysql database index. Full-text indexing can quickly implement full-text search. Its type is fulltext, next, let's take a look at how to create full-text indexes in MySQL and how to configure full-text indexes in mysql. I hope this article will help you.

Using indexes is one of the essential skills for database performance optimization. MySQL Databases have four types of indexes: clustered INDEX (primary key INDEX), common INDEX, unique INDEX, and fulltext index ).

Full-text index (also called full-text search) is a key technology currently used by seo search engines. It uses multiple algorithms, such as the word segmentation technology, to intelligently analyze the frequency and importance of keyword words in text, and then intelligently filter the desired search results according to certain algorithm rules. Here, we will not go into the underlying implementation principle. Now let's look at how to create and use full-text indexes in MySQL.

Full-text indexes can only create words with less than 3 characters in the MyISAM data table and are not included in the full-text index. You can modify the my. cnf Option

Ft_min_word_len = 3

Restart the MySQL server and use

Repair table tablename quick re-generates full-text indexes for related data tables
Select * from tablename

Where match (column1, column2) against ('word1 word2 word3 ')> 0.001
Match... against finds data records in column1 and column2 data columns that contain at least one of word1, word2, and word3 words, the data column after the match keyword must be the same as the data column for creating the full-text index. The keywords are case-insensitive and sequential. Words with less than three characters are usually ignored. The match... against... expression returns a floating point number as its own evaluate result, which reflects the matching degree between the result record and the word to be searched. If no matching record is found, or too many matching result records are ignored, the expression returns 0. Expression> 0.001 is used to exclude results records whose return value of match is 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 five most matched records. In the where clause, Kana cannot be used, so having

Create full-text Cable

In MySQL, creating full-text indexes is relatively simple. For example, we have an article table (article) with three fields: primary key ID (id), article title (title), and article content (content. Now we want to create full-text indexes on the title and content columns. The SQL statement for creating the article table and full-text indexes is as follows:

The Code is as follows:	Copy code
-- Create an 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 );

The preceding SQL example creates a full-text index when creating a table. In addition, if you want to create a full-text index for a specified field in an existing table, take the article table as an example. You can use the following SQL statement to create a full-text index:

The Code is as follows:	Copy code
-- Create a full-text index for the title and content fields of the existing article table -- The index name is fulltext_article. Alter table article Add fulltext index fulltext_article (title, content)

After creating a full-text index in MySQL, you should know how to use it now. As we all know, fuzzy queries in databases use the LIKE keyword for queries, for example:

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

So, is full-text index used in this way? Of course not. We must use the unique syntax to use full-text indexes for queries. For example, to retrieve the specified query string in the title and content columns of the article table, you can write an SQL statement as follows:

The Code is as follows:	Copy code
SELECT * FROM article where match (title, content) AGAINST ('query string ')

Note: MySQL's full-text index can only be used for data tables whose database engine is MyISAM. If it is another data engine, the full-text index will not take effect. In addition, full-text indexes provided by MySQL can only be used for full-text searches in English. Currently, full-text searches cannot be performed for Chinese. To search full-text data including Chinese characters, we need to use the Sphinx (stynix)/Coreseek technology to process Chinese characters. This site will introduce sphtracing and Coreseek in subsequent articles
.
Note 1: currently, if the full-text index provided by MySQL is used, the desired search result cannot be obtained if the length of the query string is too short. By default, MySQL full-text indexes can contain a minimum length of 4 characters. In addition, if the query string contains a Stop Word, the Stop Word is ignored.

NOTE 2: if possible, create a table and insert all data before creating a full-text index, instead of directly creating a full-text index when creating a table, because the former is more efficient than the latter's full-text index.