Re: Issue #4358 - Svn WC 1.8 upgrade from 1.7 - wrong schema

Julian Foad wrote:
> Bert Huijben wrote:
>>  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.
>
> That makes sense in itself, but then the original (wc_id, local_relpath,
> op_depth) index is redundant.  So wouldn't it be better to add
> 'parent_relpath' to the primary key:
>
> -  PRIMARY KEY (wc_id, local_relpath, op_depth)
> +  PRIMARY KEY (wc_id, parent_relpath, local_relpath, op_depth)
>
> and not have a second index?  Then there would only be one index to update, and
> all the other goodness would still be there.

Ah... but that index couldn't be used for queries that only provide a local_relpath.  Although parent_relpath is a prefix of local_relpath, a plain 'local_relpath' index is ordered by lexical order where the '/' character is not special (ordering example: foo0bar, foo/bar, fooZbar), whereas the local relpaths in a (parent_relpath, local_relpath) index would be would be in a different order (foo/bar, foo0bar, fooZbar).

I wish we indexed the tables by (parent_relpath, basename) instead of duplicating the whole parent_relpath in the local_relpath column; that would make things like this easier.

Sorry for the noise.

- Julian
Received on 2013-04-22 22:10:51 CEST