Forgot to conclude by saying the search gives you a list of words that you then need to further reduce by the actual number of characters you want to search by.
That's why storing them in reverse order might be preferable. Also, just thought of something--if you store them in reverse order, you don't need to also store them in forward order. Just reverse the strings before displaying them. ----- Original Message ----- From: "Jim Showalter" <j...@jimandlisa.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Wednesday, August 05, 2009 7:04 PM Subject: Re: [sqlite] FTS and postfix search > 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
