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

[Lapo Luchini]

Taking a better look at indexes of table revision_certs:

CREATE TABLE revision_certs (
    hash not null unique, -- hash of remaining fields separarated by ":"
    id not null,          -- joins with revisions.id
    name not null,        -- opaque string chosen by user
    value not null,       -- opaque blob
    keypair not null,     -- joins with public_keys.id
    signature not null,   -- RSA/SHA1 signature of "address@hidden:val]"
    unique(name, id, value, keypair, signature)
);
CREATE INDEX revision_certs__id_name_value ON revision_certs (id, name,
value);
CREATE INDEX revision_certs__name_value ON revision_certs (name, value);

Those are not two indexes, they are four indeed:
UNIQUE(hash)
UNIQUE(name, id, value, keypair, signature)
KEY   (id);
KEY   (name, value);

Question is: is there a reason the "big unique index" has that order?
I guess so. If that's not the case, we could simply change it to:
UNIQUE(id, name, value, keypair, signature)
and avoid both KEY(id) and the proposed KEY(id, name, value).

Side questions: why an UNIQUE on "all data" and an UNIQUE on "hash of
all data"? Assuming the hash doesn't casually collide (and we assume it
all the time) and the ':' separator is adequate they are a bit redundant.

   Lapo