When I try a similar query (i.e, no type comparison), I get the same
results as you:
sqlite> SELECT eid,type FROM EVENTS WHERE eid<=3261976 ORDER BY eid DESC
LIMIT 1;
3261976|21
CPU Time: user 0.000000 sys 0.027996
sqlite> EXPLAIN QUERY PLAN SELECT eid,type FROM EVENTS WHERE
eid<=3261976 ORDER BY eid DESC LIMIT 1;
0|0|TABLE EVENTS USING PRIMARY KEY ORDER BY
As soon as I add type criterion to the where clause, though, the
performance falls way off (the index is on events(type)):
sqlite> SELECT eid,type FROM EVENTS WHERE eid<=3261976 AND type=22 ORDER
BY eid DESC LIMIT 1;
3261891|22
CPU Time: user 3.069533 sys 0.485927
sqlite> EXPLAIN QUERY PLAN SELECT eid,type FROM EVENTS WHERE
eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE EVENTS WITH INDEX ev4_idx ORDER BY
The fact that it seems to be able to do a descending sort very quickly
if it's only using the primary key, let me to try the following, which
turned out to be more convoluted and was much slower.
sqlite> SELECT e.eid,e.type FROM events e, (SELECT eid FROM events WHERE
eid<=3261976 ORDER BY eid DESC) l WHERE e.eid=l.eid AND type=22 LIMIT 1;
CPU Time: user 29.111574 sys 3.276502
sqlite> EXPLAIN QUERY PLAN SELECT e.eid,e.type FROM events e, (SELECT
eid FROM events WHERE eid<=3261976 ORDER BY eid DESC) l WHERE
e.eid=l.eid AND type=22 LIMIT 1;
0|0|TABLE events AS e WITH INDEX ev4_idx
1|1|TABLE events USING PRIMARY KEY
It seems to me that sqlite can very efficiently do a descending sort on
a primary key by itself, but not when it's used with an index. Does
that sound correct? Also, I'm using sqlite 3.5.6, not 3.5.9. Does that
make a difference?
Thanks,
Jeff
Alexey Pechnikov wrote:
> I try with this script on my laptop with 1 Gb RAM
>
> #!/usr/bin/tclsh
> package require sqlite3
>
> sqlite3 db index_order.db
> db eval {DROP TABLE IF EXISTS events}
> db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY)}
> db eval {CREATE INDEX ev_desc_idx ON events(eid desc)}
> db transaction {
> for {set i 0} {$i<100000000} {incr i} {
> db eval {insert into events values ($i)}
> }
> }
> db close
>
> SQLite version 3.5.9 is used.
>
> I'm increasing ~ x10 rows count (and search for 32619760 row against 3261976
> in your query) and my database size is similar to your database:
> $ ls -lh .|grep db
> -rw-r--r-- 1 veter veter 2,4G Июн 29 12:08 index_order.db
>
> There are my results:
>
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 ORDER BY eid DESC
> LIMIT 1;
> 32619760
> CPU Time: user 0.000000 sys 0.000000
>
> sqlite> explain query plan SELECT events.* FROM events WHERE eid<=32619760
> ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events USING PRIMARY KEY ORDER BY
>
> Index ev_desc_idx is not used.
>
> Check your SQLite version and try again with my script.
> _______________________________________________
> 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
