I agree. If I drop indices that use "type", I get my performance back
for this query:
sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22
ORDER BY eid DESC LIMIT 1;
16643833|27906245|5972704|0|22|9|4
CPU Time: user 0.001000 sys 0.001000
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
The problem is that indexing the type column gives me a huge
performance benefit for other queries in my application. Is there any
way I can force sqlite to not use an index for a particular query?
Thanks for your help!
Jeff
On Jul 1, 2008, at 2:14 AM, Alexey Pechnikov wrote:
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
