[Monotone-devel] Re: Monotone speedup by adding additional database indices?

[Lapo Luchini]

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)