In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-21 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article is to share with you about how to achieve fuzzy search in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.
01 introduction
According to different application scenarios, MySQL supports a variety of fuzzy search methods. For example, Like matching and RegExp regular matching are probably the most widely used. Although the usage and principle of the two are very similar, the matching principles are actually different. Like requires a pattern string to match the whole target field to retrieve the record, while RegExp requires the target field to contain a pattern string.
For the simple fuzzy search to judge whether there is a type in the pattern string, it can be realized by using MySQL built-in functions, such as Instr (), Locate (), Position () and so on.
Of course, when it comes to MySQL query performance, we have to mention indexes, and for field fuzzy query requirements, we can also consider adding full-text indexes (Fulltext).
Note: MySQL version 8.0.19 and visualization tool Navicat Primium are used in this article.
02 4 kinds of fuzzy queries
In order to describe and test the results of different fuzzy query methods, a simple test data table tests is given as follows:
Where the tests table contains only one field named words, and adds a full-text index to that field. There are 6 records in the table.
Like
Like is counted as a predicate in MySQL Its application and is, =, > and OK 4 > time: 0.036s 56 16SELECT says FROM sayings WHERE LOCATE-REGEXP canonically matches 7SELECT says FROM sayings WHERE says REGEXP 'success' 8 > OK 9 > time: 0.053s 1011ML-built-in function search 12SELECT says FROM sayings WHERE INSTR (says,' success') 13 > OK 14 > time: 0.045s 15 16SELECT says FROM sayings WHERE LOCATE ('success') Says) 17 > OK 18 > time: 0.044s 19 20SELECT says FROM sayings WHERE POSITION ('success' in says) 21 > OK 22 > time: 0.047s 23 24muri-full-text index 25SELECT says FROM sayings WHERE MATCH (says) against (' Success') 26 > OK 27 > time: 0.006s
It can be seen that the speed of full-text index is the widest, which is close to one order of magnitude ahead of other methods, while the speed of Like wildcard is the second, but it is not much different from that of other query methods.
Through the Explain query plan, we can find that the full-text indexing method is fast because it applies the index without the need for full-table query, while the other three fuzzy query methods execute full-table query.
Full-text index query plan
Like wildcard query plan
In fact, when you apply an Like query to a field that adds an index, you can apply the index acceleration query. To verify whether the index can still be applied under the condition of full-text index, we conduct a second set of performance tests:
Records in query statements that begin with "success" (full-text indexing does not support query tasks starting with specified words). The execution time of the corresponding SQL statement is as follows:
1SELECT says FROM sayings WHERE says LIKE 'success%' 2 > OK 3 > time: 0.015s 4 5SELECT says FROM sayings WHERE says REGEXP' ^ success' 6 > OK 7 > time: 0.046s 8 9SELECT says FROM sayings WHERE INSTR (says, 'success') = 1 10 > OK 11 > time: 0.042s 12 13SELECT says FROM sayings WHERE LOCATE (' success') Says) = 1 14 > OK 15 > time: 0.051 s 16 17SELECT says FROM sayings WHERE POSITION ('success' in says) = 1 18 > OK 19 > time: 0.049 s 20 21SELECT says FROM sayings WHERE MATCH (says) against (' Success') 22 > OK 23 > time: 0.007s
As you can see, the efficiency of the modified Like query is significantly higher than that of other ways. However, interpreting the query plan shows that although possible_key shows the index fields, no indexes are actually applied (key is null), that is, full table queries are still performed (Type = All). The speed is greatly improved simply because string matching is much faster on 'success%' than on'% success%' (the latter matches the whole column, the former only needs to match the first word), regardless of the index.
Like'success%' still cannot apply full-text indexing
Therefore, it is concluded that the Like wildcard can not effectively use the full-text index to speed up the query, but the query speed in the specific mode can be faster than that in the wildcard% mode.
The above is how to achieve fuzzy search in MySQL, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.