To enable and use MySQL full-text index, 1. Make sure that the table engine is InnoDB or MyISAM, add FULLTEXT index when creating or modifying tables; 2. Use MATCH...AGAINST syntax to perform searches, default natural language mode, and use Boolean mode to improve flexibility; 3. Pay attention to keyword length, common word limitations and matching issues, and adjust ft_min_word_len, use Boolean mode or combine sorting optimization results; 4. In terms of performance, avoid frequent updates of fields to build indexes, control the number of index fields and maintain them regularly; 5. Chinese support is weak, and can be solved through ngram plug-in, application-layer word segmentation or external search engines.

MySQL's full-text search function is very practical when dealing with text search needs, especially in scenarios such as blogs, forums, e-commerce, etc. where content needs to be quickly searched. Although it is not as powerful as a professional search engine, it is perfectly enough for small and medium-sized projects and is relatively simple to implement.

How to enable and use MySQL full-text indexing?

To use full-text search, you must first make sure your table engine is InnoDB or MyISAM (InnoDB is recommended), and then add a FULLTEXT index when creating a table or modifying the table structure.

For example:

 CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
) ENGINE=InnoDB;

After inserting the data, you can use MATCH ... AGAINST syntax to perform full text search:

 SELECT * FROM articles WHERE MATCH(title, body) AGAINST('keyword');

The default is natural language mode, and you can also specify other modes, such as Boolean mode:

 SELECT * FROM articles WHERE MATCH(title, body) AGAINST('Keyword*' IN BOOLEAN MODE);

Boolean mode supports more flexible query methods, such as wildcards, exclusion words, etc.

FAQs and Optimization Suggestions

The query results are not ideal?

Sometimes you will find that you can't search for what you expect, which may be due to the following reasons:

• The keyword is too short, the default minimum length is 4, which can be adjusted by ft_min_word_len ;
• Search terms appear in more than 50% of records and are considered "common words" and ignored;
• The natural language pattern is used, and the matching degree is not high enough.

suggestion:

• If your business has a lot of short keywords, such as technical terms, abbreviations, etc., it is recommended to change ft_min_word_len to 3 or lower;
• For low matching problems, you can try Boolean mode to improve accuracy with operators;
• You can sort it in combination with ORDER BY MATCH... AGAINST() to make the more relevant rank ahead.

What should I do if I can’t improve my performance?

Although the full text index is fast, it is not omnipotent. If your data volume is large and concurrency is high, you should still pay attention to some details:

• Avoid creating full-text indexes on frequently updated fields;
• Reasonably control the number of index fields and do not add them all;
• Do ANALYZE TABLE and OPTIMIZE TABLE regularly to keep index statistics accurate;
• Don't expect it to replace professional search engines like Elasticsearch. It's better to leave complex scenarios to them.

How about Chinese support?

MySQL native full-text search supports Chinese weakly because it relies on the space word segmentation mechanism, while Chinese has no spaces. So if you use it directly, you will find that searching for Chinese is basically invalid.

Solution:

• Use third-party plugins such as ngram word segmenter (for MySQL 5.7);
• At the application layer, participle words by themselves, save the words in separate fields and then create a full text index;
• Or simply use external search engines, such as Elasticsearch Logstash to import data.

Basically that's it. MySQL's full-text search function can save a lot of trouble if it is used well, but don't expect too much. The key is to choose a reasonable plan based on the actual scenario.

The above is the detailed content of MySQL Full-Text Search Implementation and Tuning. For more information, please follow other related articles on the PHP Chinese website!