There seems to be an issue with the sqlite cost heuristic with
an INDEX present on GROUP BY with certain types of WHERE clauses.
Given the database formed by running these statements:
create table stuff(a,b,c,d);
insert into stuff values(1,2,3,4);
create temp view v1 as select random()%100,
random()%100, random()%1000, random()%10000
from stuff x, stuff y;
insert into stuff select * from v1;
insert into stuff select * from v1;
insert into stuff select * from v1;
insert into stuff select * from v1;
insert into stuff select * from v1;
create index stuff_b on stuff(b);
create index stuff_c on stuff(c);
create index stuff_d on stuff(d);
analyze;
Using sqlite.org's sqlite3-3.5.4.bin, this query takes 47 seconds:
select c from stuff where +b=23 group by c;
while this query takes just 2 seconds:
select c from stuff where +b=23 group by +c;
It is more efficient in this case to do a full table scan instead
of using the INDEX on column c.
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
