Mailing-List: contact dev-help@subversion.apache.org; run by ezmlm
Precedence: bulk
Received-SPF: pass (athena.apache.org: local policy)
Message-ID: <4E88BE1C.6030502@mark.mielke.cc>
Date: Sun, 02 Oct 2011 15:40:12 -0400
From: Mark Mielke <mark@mark.mielke.cc>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:6.0.2) Gecko/20110906 Thunderbird/6.0.2
MIME-Version: 1.0
To: =?UTF-8?B?QnJhbmtvIMSMaWJlag==?= <brane@xbc.nu>
CC: dev@subversion.apache.org
Subject: Re: SQL indices a WC format bump and 1.7
References: <87fwkf9fur.fsf@stat.home.lan> <4E88BAD1.6080306@xbc.nu>
In-Reply-To: <4E88BAD1.6080306@xbc.nu>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 10/02/2011 03:26 PM, Branko Čibej wrote:
> On 02.09.2011 16:30, Philip Martin wrote:
>> Bert also suggests changing our other indices by adding wc_id and/or
>> local_relpath thus allowing them to be UNIQUE.  Can anyone confirm that
>> UNIQUE indices are better?
> Just imagine, if the UNIQUE constraint did not imply an index, every
> INSERT or UPDATE would have to scan the whole table in order to verify
> the constraint. That would be ... less than efficient.
>

Just in case it is useful to consider:

Sophisticated database engines generally have a "planning" phase and a 
"execution" phase. The planning phase takes the query and attempts to 
determine the most efficient plan to execute your query. Part of this 
planning effort involves determining whether they key you are looking up 
has high selectivity or low selectivity. How many tuples in the table 
will have a matching key?

For non-unique indexes, the database engine either needs to guess or it 
needs to check against some statistical analysis results done on the 
table to see whether the key looks like it will have high selectivity or 
low selectivity. Either it could guess wrong, or it could increase the 
planning time.

For unique indexes, it can assume that there will be only 0 or 1 results.

Therefore, if you have a key which is unique, you really should define 
it as such.

I don't know if SQLlite is sophisticated enough for the above to matter 
or not, though. For example, it might assume low selectivity and it 
makes no difference.

The statement about adding key fields to make the key be unique confuses 
me a bit, though. Adding fields to the key will generally make the index 
larger and the lookups slower. In some databases that are able to do 
lookups using only the index and return results from this index - 
including all necessary data for the query (matching fields to start, 
and returning fields at the end) can be a speedup, but I would normally 
assume this was not true until proven that it was true.

Anyways - I'm not familiar with the exact scenario you are talking 
about. Just wishing to help...

-- 
Mark Mielke<mark@mielke.cc>