[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Monotone-devel] Monotone speedup by adding additional database indi
From: |
Ralf S. Engelschall |
Subject: |
Re: [Monotone-devel] Monotone speedup by adding additional database indices? |
Date: |
Thu, 11 Oct 2007 09:32:59 +0200 |
User-agent: |
Mutt/1.5.16 OpenPKG/CURRENT (2007-06-09) |
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
[Monotone-devel] Re: Monotone speedup by adding additional database indices?, Lapo Luchini, 2007/10/11