I'm including a copy of Alexey's relevant message below. Unless I
misunderstand, he has a test case that demonstrates that for the table:
CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)
the query:
SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY
eid DESC LIMIT 1;
runs much faster if there is no index on type. The culprit seems to be
the <= in conjunction with the descending ordering. If you change
either, the query gets much faster. He tried the using indices
events(type,eid) and events(type,eid desc). I also tried your original
suggestion of just events(type) and got the same result.
Thanks,
Jeff
D. Richard Hipp wrote:
> On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote:
>
>> Is it a problem in sqlite that it will only optimize: "WHERE
>> primary_key<=X ORDER BY primary_key DESC" if it's not using an index?
>> Is it supposed to?
>>
>
> It would be a problem if it where the case. But in every test I have
> tried, SQLite does in fact use an index on WHERE pk<=X ORDER BY pk
> DESC. If you can demonstrate a case where it does not, we will fix it.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
