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.