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