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