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

[Lapo Luchini]

Lapo Luchini wrote:
> (some kind of auto-resolve rootpage->name of index/table would be NICE
> on sqlite-client side, tough :P)

Small PHP script to aid in the display of EXPLAIN:

% cat explain.php
<?
$db = new PDO("sqlite:$argv[1]") or die("Error in open.\n");
$q = $db->query("EXPLAIN $argv[2];") or die("Error in query.\n");
$q->setFetchMode(PDO::FETCH_NUM);
print("addr opcode         p1   p2   p3\n");
print("---- -------------- ---- ---- ---------------------------\n");
while ($r = $q->fetch()) {
  printf("%4d %-14s %4d %4d %s\n", $r[0], $r[1], $r[2], $r[3], $r[4]);
  if (substr($r[1], 0, 4) == 'Open') {
    $q2 = $db->query("SELECT name FROM sqlite_master WHERE rootpage =
$r[3];");
    $r2 = $q2->fetch(PDO::FETCH_NUM);
    printf("                         ^^^^ %s\n", $r2[0]);
    $q2->closeCursor();
  }
}
?>

% php explain.php .monotone/net.venge.mtn "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)
                         ^^^^ sqlite_autoindex_revision_certs_2
   3 SetNumColumns     1    6
   4 String8           0    0 def
   5 IsNull           -1   20
   6 String8           0    0 abc
   7 IsNull           -2   20
   8 String8           0    0 ghi
   9 IsNull           -3   20
  10 MakeRecord        3    0 bbbbb
  11 MemStore          0    0
  12 MoveGe            1   20
  13 MemLoad           0    0
  14 IdxGE             1   20 +
  15 Column            1    1
  16 Column            1    0
  17 Column            1    2
  18 Callback          3    0
  19 Next              1   13
  20 Close             1    0
  21 Halt              0    0
  22 Transaction       0    0
  23 VerifyCookie      0   39
  24 Goto              0    1
  25 Noop              0    0