Fuzzy query optimization notes for MySQL millions data

Recently the boss gave a demand, is to write a name of fuzzy query.

The problem is simple, the difficulty is that the table has nearly 500W data.

If you want to do Chinese fuzzy query, the efficiency is simply appalling.

I looked up the information on the Internet and found that the full-text index was quite in line with my needs.

As a result, use it down. It doesn't really fit my requirements.

The smallest unit of a full-text index is a word, which cannot be queried if it is not in the in Boolean mode

Second, the full-text index only supports semi-fuzzy query, I called the semi-fuzzy is xx like ' xx% '

If you use the full fuzzy query, after the index has been built, using like or using regexp in common keywords is really much faster.

Can be controlled at about 0.1

But the low-frequency word is still very slow needs 10S

So, based on this situation, it might be better to do a sub-table.

or modify requirements to do semi-fuzzy queries.

Here is the reference material

Full-Text Search in MySQL (1)

MySQL uses full-text indexing (fulltext index)

Fuzzy query optimization notes for MySQL millions data