On Thu, Aug 5, 2010 at 1:37 PM, Simon Slavin <slav...@bigfraud.org> 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 the name like > that, give it a column of its own with its own index.
That's a good idea. I think it would help a lot with row fetching if section was it's own column: select * from my_table where section is "g" and name like "%e%" order by name limit 1 offset 4; 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; Cheers, Sam _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users