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 letter is all you are after, either 'starts with' or 'contains' and not in order combinations.)
Adam On Thu, Aug 5, 2010 at 5:40 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 > section; > > But 'group by section' can profit from the index on the section column so > it should be faster. > > As with all these things, the suggestion is to try it and see. You should > try six or seven different solutions including shuffling columns and indexes > before you settle on the one that will be in your final code. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
