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