You could store the words reversed (in addition to storing them in forward order). Then like 'xxx%' would be fast.
This would double your disk footprint, but could give you the search performance you're looking for. If that's too goofy, you could create a table of all one, two, and three-character word endings, and join to it from all of your words (stored in forward order). Then search first for the primary key of the word ending you want to search for, then search your words for that key. Index the join. ----- Original Message ----- From: "Lukas Haase" <lukasha...@gmx.at> To: <sqlite-users@sqlite.org> Sent: Wednesday, August 05, 2009 6:16 PM Subject: Re: [sqlite] FTS and postfix search > Wes Freeman schrieb: >> I clearly am not in the right mindset to be answering list emails. >> Please ignore my response (it's too late now)--back to my stressful >> deadline. > > :-) > >> Strange that it's implemented for prefix and not postfix? > > Well, an explanation is easy: Same as with LIKE, LIKE 'xxx' or LIKE > 'xxx%' can be performed easy because only the beginning of words > need to > be compared. > > However, there /is/ a way to also do postfix searches. I have the > *same* > database in *.hlp format and with WinHelp it's possible to search > '*otor' (and others) with almost zero CPU and time consumption. I'd > be > curious how they did this. > > For a solution for SQLite I would accept a small performance penalty > in > that case (but very few secs max); additionally I would also accept > the > index being bigger. > > Regards, > Luke > >> Wes >> >> On Wed, Aug 5, 2009 at 8:58 PM, Lukas Haase<lukasha...@gmx.at> >> wrote: >>> Wes Freeman schrieb: >>>> Why not LIKE '%otor'? >>> SELECT topic_title FROM topics >>> WHERE topic LIKE '%otor%' >>> ORDER BY topic_title ASC; >>> >>> This is very, very slow, especially on my > 100 MB database. >>> "Realtime" >>> search in the GUI is a requirement. This is exactly the reason why >>> I >>> want to use FTS instead of LIKE... >>> >>> Regards, >>> Luke >>> >>>> Wes >>>> >>>> On Wed, Aug 5, 2009 at 7:47 PM, Lukas Haase<lukasha...@gmx.at> >>>> wrote: >>>>> Hi, >>>>> >>>>> It's me again, sorry. The next big problem concerning FTS. I >>>>> have the >>>>> requirement to do postfix searches, like: >>>>> >>>>> SELECT topic_title FROM topics >>>>> WHERE topic MATCH '*otor' >>>>> ORDER BY topic_title ASC; >>>>> >>>>> should find Motor, motor, Monotor etc. But this does not seem to >>>>> work. >>>>> Is there any chance to get this working? >>>>> >>>>> Best regards, >>>>> Luke >>>>> >>>>> _______________________________________________ >>>>> sqlite-users mailing list >>>>> sqlite-users@sqlite.org >>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
