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
