Hi all, I'm seeing high disk IO and associated processing overhead in the following situation, which shouldn't be as expensive as it currently is.
Schema: CREATE TABLE symbol (file integer NOT NULL REFERENCES file (id), symbol varchar NOT NULL, revision varchar NOT NULL, branch boolean NOT NULL, UNIQUE (file, symbol)); Query: SELECT DISTINCT symbol FROM symbol; Query plan: 0|Trace|0|0|0||00| 1|OpenEphemeral|1|2|0|keyinfo(1,BINARY)|00| 2|Integer|0|3|0||00| 3|Integer|0|2|0||00| 4|Gosub|5|34|0||00| 5|Goto|0|37|0||00| 6|OpenRead|0|5|0|2|00| 7|Rewind|0|13|0||00| 8|Column|0|1|8||00| 9|Sequence|1|9|0||00| 10|MakeRecord|8|2|10||00| 11|IdxInsert|1|10|0||00| 12|Next|0|8|0||01| 13|Close|0|0|0||00| 14|Sort|1|36|0||00| 15|Column|1|0|7||00| 16|Compare|6|7|1|keyinfo(1,BINARY)|00| 17|Jump|18|22|18||00| 18|Move|7|6|1||00| 19|Gosub|4|29|0||00| 20|IfPos|3|36|0||00| 21|Gosub|5|34|0||00| 22|Column|1|0|1||00| 23|Integer|1|2|0||00| 24|Next|1|15|0||00| 25|Gosub|4|29|0||00| 26|Goto|0|36|0||00| 27|Integer|1|3|0||00| 28|Return|4|0|0||00| 29|IfPos|2|31|0||00| 30|Return|4|0|0||00| 31|SCopy|1|11|0||00| 32|ResultRow|11|1|0||00| 33|Return|4|0|0||00| 34|Null|0|1|0||00| 35|Return|5|0|0||00| 36|Halt|0|0|0||00| 37|Transaction|0|0|0||00| 38|VerifyCookie|0|5|0||00| 39|TableLock|0|5|0|symbol|00| 40|Goto|0|6|0||00| What I expect to see is an index scan on the index of the UNIQUE constrain and picking the value without ever touch the table. Joerg _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
