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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to