Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

Were you able to solve the problem? What solution have to chosen? BR, Fedor I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or sqlite expert, so I might be thinking about it all wrong. I have something like a (read-only) address book/rolodex, with interactive

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

Subject: EXTERNAL:Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way? First, permit me a little rant. As a user, I dislike this kind of incremental search feature if there's no easy way to toggle it or to configure it and the lis

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

First, permit me a little rant. As a user, I dislike this kind of incremental search feature if there's no easy way to toggle it or to configure it and the list of items will be large enough to cause a typing lag. The feature can become an intrusive nuisance, the opposite of what is intended.

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts wrote: > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess wrote: >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts wrote: >>> FTS3 only searches full terms/words by default, but I think if I built a

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

Have you not consider loading the whole rows into memory array and use simple string search or regexp? I'm sure 10,000 records could be search a blink. best regards, Radzi. On 6-Aug-2010, at 3:42 AM, Sam Roberts wrote: > I'd appreciate any suggestions on good ways to do this, I'm neither an SQL

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess wrote: > On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts wrote: >> FTS3 only searches full terms/words by default, but I think if I built a >> custom >> tokenizer that returned all the suffix trees for a name: > >

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts wrote: > FTS3 only searches full terms/words by default, but I think if I built a > custom > tokenizer that returned all the suffix trees for a name: FTS3 can do prefix searches, MATCH 'a*'. Also, it aimed to support multiple

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Fri, Aug 6, 2010 at 6:11 AM, Adam DeVita wrote: > A variant on Simon's plan. > Are the 10,000 rows static, slowly changing, or frequently changing? Never change, it's read-only. >  Does > it make sense to pre-calculate some counts at the time data is loaded? >  Is >

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

A variant on Simon's plan. Are the 10,000 rows static, slowly changing, or frequently changing? Does it make sense to pre-calculate some counts at the time data is loaded? Is this memory constrained so much that you can't afford 1 or 2 MB to let you look up based on ints? (I'm assuming that one

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On 5 Aug 2010, at 10:03pm, Sam Roberts wrote: > But do you think the section would make the counting faster? I think > I'd have to get the row counts like this, which would still do the > slow full table scan: > > select section, count(*) from my_table where name like '%e%' group by >

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On Thu, Aug 5, 2010 at 1:37 PM, Simon Slavin wrote: > > On 5 Aug 2010, at 8:42pm, Sam Roberts wrote: > >> select substr(name,1,1), count(*) from my_table where name like '%e%' >> group by substr(name,1,1); > > If you are constantly going to be using the first character of

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

On 5 Aug 2010, at 8:42pm, Sam Roberts wrote: > select substr(name,1,1), count(*) from my_table where name like '%e%' > group by substr(name,1,1); If you are constantly going to be using the first character of the name like that, give it a column of its own with its own index. Simon.

[sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or sqlite expert, so I might be thinking about it all wrong. I have something like a (read-only) address book/rolodex, with interactive searching. As users type into the search box, I need to first know for each section