Re: [sqlite] FTS and postfix search

"Hugh Sasse" schrieb im Newsbeitrag news:alpine.lfd.2.00.0908061712390.30...@trueman.cs.cse.dmu.ac.uk... > I wonder if the idea of suffix arrays would belp. > http://www.cs.dartmouth.edu/~doug/sarray/ I was about suggesting basically the same thing... In case of Lukas'

Re: [sqlite] FTS and postfix search

words you are searching. - Original Message - From: "Jim Showalter" <j...@jimandlisa.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Thursday, August 06, 2009 8:59 AM Subject: Re: [sqlite] FTS and postfix search >

Re: [sqlite] FTS and postfix search

ch 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-use

Re: [sqlite] FTS and postfix search

e" <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. >>

Re: [sqlite] FTS and postfix search

Roger Binns schrieb: > [...] > > Lukas Haase wrote: >> additionally I would also accept the index being bigger. > > You could have a second FTS table where you store the keywords in reverse > order :-) Hi, DAMN!! Damn, damn. Thank you for the great idea. But unfortunately I thought I could

Re: [sqlite] FTS and postfix search

On 6/08/2009 12:07 PM, Jim Showalter wrote: > Sorry--I read my emails arrival order, not reverse chronological--so I > didn't see that John had already solved it. Not me ... this is ancient lore e.g. Knuth vol 3 of TAOCP 1973 edition page 391 "If we make two copies of the file, one in which the

Re: [sqlite] FTS and postfix search

Wednesday, August 05, 2009 6:40 PM Subject: Re: [sqlite] FTS and postfix search > On 6/08/2009 11:16 AM, Lukas Haase wrote: >> Wes Freeman schrieb: > >> >>> Strange that it's implemented for prefix and not postfix? >> >> Well, an explanation is easy: Same a

Re: [sqlite] FTS and postfix search

ust 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

Re: [sqlite] FTS and postfix search

sha...@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 n

Re: [sqlite] FTS and postfix search

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Lukas Haase wrote: > additionally I would also accept the index being bigger. You could have a second FTS table where you store the keywords in reverse order :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using

Re: [sqlite] FTS and postfix search

On 6/08/2009 11:16 AM, Lukas Haase wrote: > Wes Freeman schrieb: > >> 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. >

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'

Re: [sqlite] FTS and postfix search

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? Wes On Wed, Aug 5, 2009 at 8:58 PM, Lukas Haase wrote: > Wes Freeman

Re: [sqlite] FTS and postfix search

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

Re: [sqlite] FTS and postfix search

Why not LIKE '%otor'? Wes On Wed, Aug 5, 2009 at 7:47 PM, Lukas Haase 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

[sqlite] FTS and postfix search

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