MySQL Full-text index

Using indexes is one of the essential skills of 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 search engines at present. It can use "word segmentation technology" and other algorithms to intelligently analyze the text of the key words in the frequency and importance, and then follow certain algorithm rules to intelligently filter out the search results we want. Here we don't go into the bottom-up principle of implementation, and now let's look at how to create and use full-text indexing in MySQL.

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

2. --Create a article table
   
4. CREATE TABLE Article (
   
6. ID INT UNSIGNED auto_increment not NULL PRIMARY KEY,
   
8. Title VARCHAR(+),
   
10. Content TEXT,
    
12. Fulltext (title, content) -- Create a full - text index on the title and content columns
    
14. );

The above is a SQL example that creates a full-text index while creating a table. Also, if we want to create a full-text index for the specified field of a table that already exists, article we can use the following SQL statement to create the table as an example:

1. --Create a full- text index for the title and content fields of an existing article table
   
2. --The index name is fulltext_article
   
3. ALTER TABLE article
   
4. ADD fulltext INDEX fulltext_article (title, content)

After you create 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 is a LIKE query using keywords, for example:

*'% query string% '

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

* FROM article WHERE MATCH(title, content) against(' query string ') 

It is strongly noted that the full-text index of MySQL can only be used for data tables that are MyISAM by the database engine, and if it is a different data engine, the full-text index will not take effect. In addition,MySQL's own full-text index can only be full-text search in English , currently cannot be full-text retrieval of Chinese. We need to use Sphinx (Sphinx)/coreseek technology to deal with Chinese if we need to do full-text retrieval of text data including Chinese. Sphinx and Coreseek will be introduced in the following articles.

Note 1: Currently, when using MySQL's own full-text index, the query string will not get the desired search results if it is too short in length. The default minimum length of a word that can be found by a MySQL full-text index is 4 characters. In addition, if the queried string contains a stop word, the stop word is ignored.

NOTE 2: If possible, try to create a table and insert all the data before creating 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 index.

This article originates from: http://www.365mini.com/page/mysql-create-fulltext-index.htm

Recommendation: http://www.cnblogs.com/tommy-huang/p/4483684.html

MySQL Full-text index