Markus Schiltknecht wrote: >> ! 2|OpenRead|1|28|keyinfo(5,BINARY,BINARY) >> ! 2|OpenRead|1|39|keyinfo(3,BINARY,BINARY) > I'm not an expert reading these plans, but for sure both variants use an > index scan and not a sequential scan.
Those are the "key rows" to understand which index is being used. Unfortunately I didn't yet manage to understand how to know WHAT index is the number referring to, but I guess this should mean, respectively: - use index 28, bind two columns with binary affinity - use index 39, bind two columns with binary affinity (what about the 5 and 3? dunno) ...so even if the rest of the code is almost identical, the index used is different, and the column that is search vs scanned is reversed. Which does all the difference here. In fact it confirms that with "standard 0.36 schema" the WRONG index is used: the fact that it is binded with TWO values is quite telling. % sqlite3 0.36.mtn sqlite> .explain on sqlite> EXPLAIN SELECT id, name, value FROM revision_certs WHERE id = 'abc' AND name = 'def' AND value = 'ghi'; addr opcode p1 p2 p3 ---- -------------- ---------- ---------- ----------------------- 0 Goto 0 22 1 Integer 0 0 2 OpenRead 1 13 keyinfo(5,BINARY,BINARY) 3 SetNumColumns 1 6 4 String8 0 0 def [...] sqlite> EXPLAIN SELECT id, name, value FROM revision_certs WHERE id = 'abc' AND +name = 'def' AND value = 'ghi'; addr opcode p1 p2 p3 ---- -------------- ---------- ---------- ----------------------- 0 Goto 0 29 1 Integer 0 0 2 OpenRead 0 11 3 SetNumColumns 0 4 4 Integer 0 0 5 OpenRead 1 38 keyinfo(1,BINARY) 6 String8 0 0 abc [...] The second query is using "+name" to be sure the (name, value) index won't be used, and in fact the OpenRead binds a different index and with only one value, which is id='abc'. sqlite> DROP INDEX revision_certs__id; sqlite> CREATE INDEX revision_certs__id_name_value ON revision_certs (id, name, value); sqlite> vacuum; sqlite> analyze; sqlite> .explain off sqlite> SELECT name, rootpage FROM sqlite_master WHERE type = 'index' AND tbl_name = 'revision_certs'; [...] revision_certs__name_value|38 revision_certs__id_name_value|40 sqlite> .explain on sqlite> EXPLAIN SELECT id, name, value FROM revision_certs WHERE id = 'abc' AND name = 'def' AND value = 'ghi'; addr opcode p1 p2 p3 ---- -------------- ---------- ---------- ------------------------- 0 Goto 0 22 1 Integer 0 0 2 OpenRead 1 40 keyinfo(3,BINARY,BINARY) ...and 40 is indeed the rootpage of the new index. (some kind of auto-resolve rootpage->name of index/table would be NICE on sqlite-client side, tough :P) _______________________________________________ Monotone-devel mailing list Monotone-devel@nongnu.org http://lists.nongnu.org/mailman/listinfo/monotone-devel
