Kurt,
Thanks, the single primary index you suggested does give us the
desired results.
Steve
Kurt Welgehausen wrote:
Steve Green <[EMAIL PROTECTED]> wrote:
CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id );
...
CREATE INDEX ix_data_ut on data( u_id, utime );
Wouldn't a unique
Steve Green <[EMAIL PROTECTED]> wrote:
> the unary + operator in front of the two utime terms did cause the optimal
> index to be used... Can you explain why this works?
A unary + in front of a column name disqualifies the term from
consideration by the optimizer, and hence from use by indices.
Steve Green <[EMAIL PROTECTED]> wrote:
> CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id );
> ...
> CREATE INDEX ix_data_ut on data( u_id, utime );
Wouldn't a unique index on (u_id, utime, r_id) get you
the pk constraint and better performance on your query
with no other index to confuse
Richard,
Thanks for the reply. I did run ANALYZE and that didn't help. However,
the unary + operator in front of the two utime terms did cause the optimal
index to be used... Can you explain why this works?
I also realized that if I change the "where" clause to something like
where u_id >= 0
Steve Green <[EMAIL PROTECTED]> wrote:
[...]
>
> select u_id, sum( data1 ), sum( data2 )
> from data where utime >= 1146441600 and utime < 114912
> group by u_id
> order by sum( data1 ) desc
> limit 10
>
[...]
>
> So, is there a way that I can get sqlite to use the optimal index
> without
Hi,
I have the following schema
CREATE TABLE data(
utime int4,
r_id int2,
u_id int4,
data1 int8,
data2 int8
);
Each row is uniquely defined by utime, r_id, and u_id, so I have the
following index
CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id );
This index is also
6 matches
Mail list logo
