Really, there is problem with multi-column indexes. You must use only primary
key index for ">=" where clause and "ASC" sorting and "<=" where clause and
DESC sorting.
1. I try with primary key:
#!/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,type INTEGER)}
db transaction {
for {set i 0} {$i<100000000} {incr i} {
set type [expr {$i % 50}]
db eval {insert into events values ($i,$type)}
}
}
db close
So, "type" is equal ("eid" mod 50).
sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY
eid DESC LIMIT 1;
32619722|22
CPU Time: user 0.000000 sys 0.000000
sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and
type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events USING PRIMARY KEY ORDER BY
========
Result: this index is good.
========
2. And I try with two-columns common order index:
#!/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,type INTEGER)}
db transaction {
for {set i 0} {$i<100000000} {incr i} {
set type [expr {$i % 50}]
db eval {insert into events values ($i,$type)}
}
}
db eval {CREATE INDEX ev_idx ON events(type,eid)}
db close
sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY
eid DESC LIMIT 1;
32619722|22
CPU Time: user 1.400088 sys 1.696106
sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and
type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events WITH INDEX ev_idx ORDER BY
========
Result: this index is bad.
========
3. And I try with two-columns desc order index:
#!/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,type INTEGER)}
db transaction {
for {set i 0} {$i<100000000} {incr i} {
set type [expr {$i % 50}]
db eval {insert into events values ($i,$type)}
}
}
db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)}
db close
sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY
eid DESC LIMIT 1;
32619722|22
CPU Time: user 0.600037 sys 0.608038
sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and
type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY
And with modified query:
sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY
eid DESC LIMIT 1;
99999972|22
CPU Time: user 0.000000 sys 0.000000
sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY
eid ASC LIMIT 1;
22|22
CPU Time: user 0.000000 sys 0.004000
sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY
eid ASC LIMIT 1;
32619772|22
CPU Time: user 0.284018 sys 0.820051
========
Result: this index is bad.
========
P.S. Try with primary key index only and write your results.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
