> -----Original Message-----
> From: Julian Foad [mailto:julianfoad_at_btopenworld.com]
> Sent: maandag 22 april 2013 20:47
> To: Bert Huijben
> Cc: 'Subversion Development'
> Subject: Re: Issue #4358 - Svn WC 1.8 upgrade from 1.7 - wrong schema
>
> Bert Huijben wrote:
>
> > Julian Foad wrote:
> >> --- schema-1.7.8-upgraded-to-1.8-dev
> >> +++ schema-1.8-dev
> >>
> >> - file_external TEXT,
> >> + file_external INTEGER,
> >
> > This doesn't matter for our use of Sqlite. We only use NULL vs set, but
> > integer documents what we now store in file_external.
> >
> > Sqlite doesn't implement an ALTER table statement that can update this,
so
> I
> > don't think we should try to change this for 1.8.
>
> >> -CREATE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (
> >> - wc_id, parent_relpath);
> >> +CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (
> >> + wc_id, parent_relpath, local_relpath);
> >>
> >> -CREATE INDEX I_NODES_PARENT ON NODES (
> >> - wc_id, parent_relpath, op_depth);
> >> +CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (
> >> + wc_id, parent_relpath, local_relpath, op_depth);
>
> For 1.8, what's the point of still including 'parent_relpath' in the
index, when
> we know that every 'local_relpath' value starts with
> 'parent_relpath'? Doesn't that just make the index a bit bigger and a bit
> slower than
>
> CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (
> wc_id, local_relpath, op_depth);
We also have that index..., but we query nodes in 3 ways:
* Where local_relpath = 'something' (exact lookup)
* Where parent_relpath = 'something' (everything in a directory)
* Where local_relpath > '...../' and local_relpath < '....0' (all
descendants)
This index is used for that second variant and by making it a unique index
instead of one with the same value multiple times it provides a stable
order, cheaper index-updates (via exact lookup) and cheaper lookups for the
case where we only want values that are cached in the index.
>
> ?
>
> > I'll look into these tomorrow, but the performance difference is not
that
> > large. And as the names are 100% identical they can never cause problems
> > upgrading to future versions. (And SQL guarantees that this can't affect
our
> > queries in any other way than performance)
>
> BTW, Brane has started working on this issue.
There should be some examples in earlier format bumps where we drop one
index and add another one.
This change can safely made part of the last existing format bump for 1.8.
Bert
Received on 2013-04-22 21:00:35 CEST