An alternate to these trigram indices is a schema of the form ``` table word (id, string); table suffix (id, string); table link (suffix, word); index link_s on link (suffix); ``` The basic idea behind this is that any substring S of a word W is the prefix of a suffix of W. Whenever a word W is added to `word`, all the suffices of W are added to `suffix`, and `link` is extended also, to map from the suffices to W. Now a substring search for the words matching `%foo%` becomes a prefix search `foo%` in `suffix`, and we get the relevant word id by joining `link`. Roughly: ``` SELECT DISTINCT word FROM link WHERE suffix IN (SELECT id FROM suffix WHERE suffix LIKE 'foo%') ``` Note, this gives us only the word ids. To get the actual words we have to join `word` as well. That exercise is left for the reader.