Geek Answers Handbook

MySQL table optimization for substring search (one dictionary of words - a dictionary)?

How to optimize a MySQL table with approximately 1 million records to search for a substring (% xxx, xx%,% xxx%)? All entries contain only one word (11 avg characters, 41 max).

I know that requesting LIKE% xxx is a problem, but I see no way to avoid this.

So the question is: is there a way to help MySQL minimize the effort for these queries? Or is there any other way how to query such data in different ways to use some kind of index?

Available technologies: MySQL, PHP, Javascript (MySQL and PHP are commercially used, so it is not possible to redirect a specific path).

background: A "complete" list of unique words used over the past 15 years in literature written in my own language. I want to give users the opportunity to find all the relevant words by entering only part of the word (any part).

+4
source share
1 answer

You cannot use the standard MySQL index for a substring. It will not work for anything other than prefix matches.

You may be able to create SOUNDEX()for the word, but that is probably not what you want.

You can create all possible substrings for each row and save them in another table. This will be many lines (maybe 50 million), especially if you include individual characters as substrings (EDIT: see below)

, , . PHP. FREJ - - Java.

:

1M * 11 = 22 (.. ).

.

: , , . n , n - .

, n- N-gram

+1

More articles:


All Articles