How to perform fast nickname search fuzzy query in mysql

WBOY
Release: 2016-09-21 14:13:05
Original
1134 people have browsed 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?

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>
Copy after login

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

Related labels:
php
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!