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

[Ralf S. Engelschall]

On Wed, Oct 10, 2007, Nathaniel Smith wrote:

> [...]
> [Err... though... waittasec.  Shouldn't the database upgrade machinery
> be causing mtn to bomb out on your modified database ("unrecognized
> schema version" or the like)?  Did you disable that or something?]

Monotone showed just _warning_ messages but operated just fine.

> I do wonder where the benefit is coming from in this particular case.
> The index on public_keys is almost certainly just irrelevant (though
> it doesn't hurt and adds scalability), since you probably don't have
> more than, say, 100 keys in there, and the whole table is almost
> certainly cached.
>
> And on revision_certs we already have:
>
> CREATE INDEX revision_certs__id ON revision_certs (id);
> CREATE INDEX revision_certs__name_value ON revision_certs (name, value);
>
> So if we add an index on (id, <anything>) we should remove the index
> on simple (id).

In general, yes. The (id, name, value) index should be consulted
by SQLite also in case only "id" has to be looked up for a query.

> But also that index on simple (id) should be making
> that search fast already, because once you've located the certs
> for a particular rev, then you only have to do a sequential scan over
> 4 of them (in most cases) to find any particular one.  Perhaps
> sqlite's optimizer has gotten *too* smart and is picking the wrong
> index, doing a lookup by (name, value) and then sequential scan to
> match the id?
> [...]

We can check this with a manual "EXPLAIN <query>", I think.

                                       Ralf S. Engelschall
                                       address@hidden
                                       www.engelschall.com