Thanks for your help. I created the index:
CREATE INDEX ev4_idx ON event(type);
According to "EXPLAIN QUERY PLAN", it's being used. When I run the query:
SELECT events.* FROM events WHERE ( events.type=22) AND
(events.eid<=3261976) AND (tid=9) AND (verbose<=1) ORDER BY events.eid
DESC LIMIT 1;
It takes about 10 seconds. If I remove the tid and verbose clauses
conditions, it changes very little (maybe 9 seconds). If I switch the
ordering to ascending, though, the query is seemingly instantaneous.
Similarly, if I compare an eid for equality, the query is nearly
instantaneous. This is part of a GUI application, so a 10-second delay
is highly undesirable. Any other suggestions?
Thanks,
Jeff
D. Richard Hipp wrote:
> On Jun 27, 2008, at 6:28 PM, Jeff Gibson wrote:
>
>
>> I have a large table and a two column index:
>>
>> CREATE TABLE events (eid INTEGER PRIMARY KEY,
>> time INTEGER,
>> aid INTEGER,
>> subtype INTEGER,
>> type INTEGER,
>> tid INTEGER,
>> verbose INTEGER);
>>
>> CREATE INDEX ev4_idx ON events (type,eid)
>>
>> When I do the following query:
>>
>> SELECT events.* FROM events WHERE ( events.type=22) AND
>> ( events.tid=9)
>> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid
>> DESC LIMIT 1;
>>
>> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC",
>> it's very fast. The query plan for both ascending and descending
>> sorts
>> both say:
>>
>> 0|0|TABLE events WITH INDEX ev4_idx ORDER BY
>>
>> For my application, I sometimes need the first and sometimes need the
>> last match. I tried selecting MAX(eid) instead of using an ORDER BY,
>> but the speed was about the same. Is there any way I can get sqlite
>> to
>> use the index for the descending order-by? Do I need a different
>> index? Or are there any other suggestions?
>>
>
> Every index includes the INTEGER PRIMARY KEY as its last term. So the
> second term in your index is redundant. It might be confusing
> things. I suggest you set up your index as simply:
>
> CREATE INDEX ev4_idx ON event(type);
>
> Or perhaps:
>
> CREATE INDEX ev4_idx ON event(type, tid);
>
> Try that and see if it works better for you.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
