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?
    Thanks,
    Jeff
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to