Some summary of MySQL's fuzzy query like

1. Common usage:

(1) Use with %

% represents a wildcard of one or more characters, for example, to query the data starting with a capital letter in the field name:

(2) Use with

_ represents a wildcard of just one character. If you change the % in the query above to _, you will find that only the following data can be found:

2. Using like fuzzy query will cause index failure and performance problems when the amount of data is large

(1) Avoid fuzzy queries that begin with % or _

By explaining the execution plan, we found that when using like fuzzy query, if the query does not start with % and _, the index is still valid

If the query starts with % or _, the index will be invalid.

(2) Using covering indexes

When the query conditions and query results are both fields in the index, this index can be called a covering index. At this time, using the like fuzzy query index is effective.

InnoDB primary key can not be added to the index

Note: When using a covering index, the length of the field is limited by requirements. Generally, if the length is exceeded, the index will become invalid.

If I include the description field in my query, the covering index will also fail (my database has been tested and only supports fields with a maximum length of 255)

(3) Use full-text indexing

Create a Full Text index for the field, and then use match(...) against(...) to search

Note: This full-text indexing method only works for English words, and is not friendly enough for Chinese characters. You need to make some configuration changes to the MySQL configuration file to make it support Chinese characters.

(4) Use some additional full-text search engines to solve

Lucene, Solr, Elasticsearch, etc.

The basic principle is: change ft_min_word_len=3 in the MySQL configuration file to 1. (If this item is not available, just add it directly), then create a new field to store the word segmentation results, and create a full-text index for this field. Then implement a word segmentation module to split the word "everyone is good" into "everyone is good, everyone is good, every family is good". Then use match .. against instead of like %%. The query result is basically the same as the result of like (if the word segmentation is reasonable), but the efficiency is at least 10 times higher than like.

Summarize

This is the end of this article about MySQL fuzzy query like. For more relevant MySQL fuzzy query like content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!