Sorry, I forgot to mention that I'm using sqlite v3.3.4 on redhat linux
v7.3
Steve
Steve Green wrote:
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 needed because at update time, I use "insert or
replace" to update the data table.
The type of query that I'm attempting to perform is similar to
select u_id, sum( data1 ), sum( data2 )
from data where utime >= 1146441600 and utime < 1149120000
group by u_id
order by sum( data1 ) desc
limit 10
My current table has about 2.5 million rows and about 86,000 distinct
u_id values in the time period selected, and the query takes about 13.5
minutes.
Performing an explain query plan reveals
0|0|TABLE data WITH INDEX pk_data
so the primary index is being used...
Based on some past experiences, I added the following index to the table
CREATE INDEX ix_data_ut on data( u_id, utime );
Note that the utime is the last parameter in the index. With the primary
index in place, I was not able to convince sqlite to use this index. To
test
the index, I was forced to drop the primary index (which I can't do in my
production environment). After dropping the primary index, an explain
query
plan revealed
0|0|TABLE data WITH INDEX ix_data_ut
and the query ran in 26 seconds...
Subsequent tests using the following indexes provided no performance
improvement
over the unique index, although sqlite's query planner chose these
indexes over
the unique index
CREATE INDEX ix_data_tu on data( utime, u_id );
CREATE INDEX ix_data_t on data( utime );
So, is there a way that I can get sqlite to use the optimal index
without having
to remove my unique index? Perhaps this would involve rewritting the
query, but
I'm at a loss as to how that could be done.
Thanks for your time,
Steve
--
Steve Green
SAVVIS
Transforming Information Technology SM
This message contains information which may be confidential and/or
privileged. Unless you are the intended recipient (or authorized
to receive for the intended recipient), you may not read, use,
copy or disclose to anyone the message or any information contained
in the message. If you have received the message in error, please
advise the sender by reply e-mail at [EMAIL PROTECTED] and
delete the message and any attachment(s) thereto without retaining
any copies.
