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