sqlite> SELECT max(eid) from events;
16643833
sqlite> SELECT count(eid) FROM events;
16643833
sqlite> SELECT count(eid) FROM events WHERE type=22;
8206183
sqlite> SELECT count(eid) FROM events WHERE eid<=3261976;
3261976
sqlite> SELECT count(eid) FROM events WHERE eid<=3261976 AND type=22;
2062728
And for performance:
sqlite> CREATE INDEX ev4_idx ON events(type);
No ordering:
sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22
LIMIT 1;
13|63922|6|0|22|9|4
CPU Time: user 0.000000 sys 0.044993
Ascending order:
sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22
ORDER BY eid ASC LIMIT 1;
13|63922|6|0|22|9|4
CPU Time: user 0.000000 sys 0.000000
Descending order:
sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22
ORDER BY eid DESC LIMIT 1;
3261891|4910298|1206924|1|22|9|4
CPU Time: user 4.204361 sys 0.885865
(wall clock time is roughly double user+sys - I guess time waiting for
disk isn't being counted in system time)
Sanity check:
sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE
eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events WITH INDEX ev4_idx ORDER BY
With a different index:
sqlite> CREATE INDEX ev5_idx ON events(type,eid desc);
sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE
eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events WITH INDEX ev5_idx ORDER BY
sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22
ORDER BY eid DESC LIMIT 1;
3261891|4910298|1206924|1|22|9|4
CPU Time: user 4.282349 sys 0.901862
(again, wall-clock time is roughly double this amount)
And ascending order is very fast:
sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22
ORDER BY eid ASC LIMIT 1;
13|63922|6|0|22|9|4
CPU Time: user 0.000000 sys 0.052992
It seems that sqlite wants to do its index scan in ascending order, so
returning the first one is very quick, but returning the last one
(first in descending order) is slow. Is there any way to give the
engine an idea that it should do its index scan in descending order so
that the ORDER BY is cheap?
Thanks,
Jeff
On Jun 28, 2008, at 11:50 AM, Alexey Pechnikov wrote:
> Show results of this queries:
>
> select max(eid) from events;
> select count(eid) from events;
> select count(eid) from events where type=22;
> select count(eid) from events where eid<=3261976;
> select count(eid) from events where eid<=3261976 and type=22;
> _______________________________________________
> 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
