How to perform fast nickname search fuzzy query in mysql

mysql performs fuzzy query on nickname search. If there are too many users, the like query will be very slow. How to solve it?

Reply content:

mysql performs fuzzy query on nickname search. If there are too many users, the like query will be very slow. How to solve it?

1. Create an index on the nickname field in this table in the database

   key `nikename`(`nikename`)

2. 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