1

I need an advice. I have a big table filled with words (lower-cased). Each row contains "id" (autoincremented int) and "word" (varchar(100)). I am looking for faster way to search all IDs for words which may include given substring. My current query looks like this:

select id from `words` where `word` like '%substring%'

Is there any way to convert this table to set of tables which can be used to search by index instead of "like"?

My only idea is to drop each word to set of substrings, for example "mysql" can be broken to "my", "mys", "mysq", "mysql", "ysql", "sql", "ql", "ys", "sq" etc. But this way I go into really big table.

Epsiloncool
  • 1,220
  • 14
  • 35
  • On a thought level, maybe you could make instances of the table each time a letter is typed bsed on the letter and then delete those instance or make them empty when done. – Taylor Courtney Mar 04 '16 at 07:16
  • Something related to match() against() in mysql ? http://stackoverflow.com/questions/6259647/mysql-match-against-order-by-relevance-and-column – rahul Mar 04 '16 at 07:16
  • 3
    Use FULLTEXT Search. Its now also possible with InnoDB – Bernd Buffen Mar 04 '16 at 07:17
  • 1
    not the answer you are looking for, but what makes `LIKE` comparison much faster is to only use one instead of two wildcards: `'substring%'` or `'%substring'` – low_rents Mar 04 '16 at 07:22
  • 2
    With just a trailing wild card Mysql can use an index. On that basis you could split up the string _Mysql_ to _Mysql_, _ysql_, _sql_, _ql_ and _l_ and then just search for like 'substring%'. If you only search for full words then you could explode the text out on spaces (and punctuation), do a keyed read for the words, and use just do a search for the phrase using your LIKE syntax only on the rows returned by the keyed read (you could also possibly cope with spelling mistakes with this kind of processing). But a full text search would probably be better. – Kickstart Mar 04 '16 at 11:50
  • 1
    Lowercasing is unnecessary if you use a "case insensitive" collation. – Rick James Mar 05 '16 at 19:09

0 Answers0