On Sep 19, 2008, at 6:30 PM, D. Richard Hipp wrote: > > On Sep 19, 2008, at 5:47 PM, Russ Leighton wrote: > >> >> Observation 1: Group by (in my case) is faster w/out using the index >> than with using the index by 10X >> >> In my app I have a table- >> >> create table foo(k1integer ,k2 integer,k3 integer,...); >> create index foo_idx on foo(k1,k2,k3); >> >> when I do- >> >> select k1,k2,sum() as s,count(1) as c from foo group by k1,k2; >> >> The query is 10X slower with the above index created as when I drop >> the index and don't use it. Bug or >> just happens to be the case for some data distribution this is just >> the way it is? Yes, I know I have >> a 3 column index and only using 2 for this query. > > I do not see how it is possible for what you say to be true - unless > you have omitted important details of your query, such as a WHERE > clause. >
Okay, this is a bit embarrassing but I can't seem to reproduce the performance difference. I spent hours tracking this issue down previously and now , going back to reproduce, I don't see it. Index'd group by's are always faster. Must have been some strange state of the equipment. Sorry for the false alarm. That said, the _real_ thing I am interested in as a requested enhancement is a way to constraint/control index selection on queries. This would give me an out should the situation above repeat itself in a more consistent manner. Thanks for sqlite. It is an amazing tool. BTW, in my mind lite != small, rather lite == simple+powerful+fast . We are using it over very large quantities of data on very powerful servers. -Russ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
