[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

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

From: Julian Foad <julianfoad_at_btopenworld.com>
Date: Mon, 22 Apr 2013 20:21:05 +0100 (BST)

Bert Huijben wrote:

> Julian Foad wrote:
>>>>  -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.

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.

- Julian
Received on 2013-04-22 21:22:01 CEST

This is an archived mail posted to the Subversion Dev mailing list.