MySQL Create full-text index

Original link: http://www.cnblogs.com/zhouguowei/p/5216316.html

One of the prerequisite skills for database performance optimization when using indexes. In the MySQL database, there are four kinds of indexes: the focused index (primary key index), the normal index, the unique index, and the full-text index (Funlltext 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 many other algorithms to intelligently analyze the frequency and importance of keywords in text words, and then follow certain algorithm rules to intelligently filter out the search results we want.

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 title of the article, and the content of the article. Now we want to be able to create full-text indexes on title and content two columns, and create SQL statements for article tables and full-text indexes as follows:

CREATE TABLE ' article ' (

' id ' int (ten) unsigned not NULL auto_increment,

' title ' varchar (+) DEFAULT NULL,

' Content ' text,

PRIMARY KEY (' id '),

Fulltext KEY ' title ' (' title ', ' content ')

) Engine=myisam DEFAULT Charset=utf8;

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

ALTER TABLE article 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 queried using the LIKE keyword, for example:

SELECT * FROM article WHERE content like '% query string% ';

So do we use full-text indexing as well? Of course not, we have to use unique syntax to query with full-text indexes, for example, we want to retrieve the specified query string in the title and content column of the article table, and we can write the SQL statement as follows:

SELECT * 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.

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

Note: If possible, try creating a table and inserting 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.

MySQL Create full-text index