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.
>
> What is the argument to sum(), btw?  You should get an error, I think.

That was just an example, not the real query. I have the issue even  
without where clause and aggregations.

Sounds like this is odd behavior. I'll try to craft a simple example  
that illustrates the 2 issues and post it.

What about the null values for the aggregation keys when I put a '+'  
to disable the index? Is that 'as designed'?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to