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_at_mielke.cc>
Received on 2011-10-02 21:40:47 CEST