Create full-text index sharing in MySQL and create full-text index in mysql

Create full-text index sharing in MySQL and create full-text index in mysql

One of the essential skills for database performance optimization when using indexes. In MySql databases, there are four types of indexes: focusing on indexes (primary key indexes), common indexes, unique indexes, and the full-text indexes (FUNLLTEXT indexes) We will introduce here ).

Full-text index (also called full-text search) is a key technology currently used by search engines. It can use multiple algorithms such as [Word Segmentation technology] to intelligently analyze the frequency and importance of keywords in text, and then intelligently filter the desired search results according to certain algorithm rules.

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:

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 preceding SQL example shows how to create 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:

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 example:

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

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

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.

Note: currently, if you use the MySql full-text index, you cannot obtain the expected search results 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: 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.