MySQL Create Full Text Index Sharing

1 of the essential skills for database performance optimization when using indexes.There are four types of indexes in the MySql database: the focused index (primary key index), the normal index, the 1-only index, and the full-text index (FUNLLTEXT INDEX) that we will describe here.

Full-text index (also called full-text search) is a key technology used by search engines at present.It can intelligently analyze the frequency and importance of keywords in text by using various algorithms, and then intelligently filter out the desired search results according to certain algorithm rules.

In MySql, it is relatively easy to create a full-text index.For example, we have an article table (article) with three fields: primary key ID (id), article title (title), article content (content).Now we want to be able to create a full-text index on columns title and content. The article table and full-text index create the following SQL statement:

CREATE TABLE `article` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(200) DEFAULT NULL,
 `content` text,
 PRIMARY KEY (`id`),
 FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The above is an SQL example of creating a full-text index while creating a table.In addition, if we want to create a full-text index on a specified field of an existing table, using the article table as an example, we can create it using the following SQL statement:

ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);

Now that you've created a full-text index in MySql, it's time to learn how to use it.It is well known that fuzzy queries in databases are queried using the like keyword, for example:

SELECT * FROM article WHERE content LIKE  ' % Query String %';

So, are we using full-text indexing the same way?Of course not, we must use specific syntax to query using full-text indexes. For example, if we want to retrieve the specified query string in full-text in the title and content columns of the article table, 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 that comes with MySql can only be used on tables where the database engine is MYISAM. Full-text indexes will not work if other data engines are involved.In addition, the full-text index that comes with MySql can only be used for full-text retrieval of English, but not for Chinese at present.If full-text retrieval of text data including Chinese is required, we need to use Sphinx (Sphinx)/Coreseek technology to process Chinese.

Note: Currently, when using the full-text index that comes with MySql, you will not get the expected search results if the length of the query string is too short.The default minimum length of words found in the MySql full-text index is 4 characters.In addition, if the string of the query contains a stop word, that stop word will be ignored.
Note: If possible, try to create a table and insert all the data before creating a full-text index, instead of directly creating a full-text index when the table is created, because the former is more efficient than the latter.