mysql performs fuzzy query on nickname search. If there are too many users, the like query will be very slow. How to solve it?
mysql performs fuzzy query on nickname search. If there are too many users, the like query will be very slow. How to solve it?
Create an index on the nickname field in this table in the database
key `nikename`(`nikename`)
Full text index with sql statement
ALTER TABLE `user` ADD FULLTEXT `fidx_user_nikename` (`nickname`)
Then query using the following method
<code>SELECT * FROM \`user\` WHERE MATCH (nickname)AGAINST ('xxx')</code>
If it is like %name%, then there is no way within the scope of my knowledge. If there is no % in front, you can build an index, or try the LOCATE function, but it probably has no effect.
Where is instr?
For fuzzy search, use word segmentation. sphinx coreseek or xunsearch(Xunsearch)
If your mysql version is greater than 5.7, it comes with ngram full-text index that supports Chinese word segmentation. I have used it in the project, and there are already tutorials on how to use it online
Use a search engine to index frequently searched fields, and then use them together with caching