Hi Jim, and thank you for the great idea. But I thought it would be possible to search '*word*' - but this is not possible with this method either.
Is there any chance for searching '*word*' quickly? Regards, Luke Jim Showalter schrieb: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
