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

Re: svn commit: r1296868 - /subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql

From: Philip Martin <philip.martin_at_wandisco.com>
Date: Tue, 06 Mar 2012 16:41:06 +0000

danielsh_at_apache.org writes:

> Author: danielsh
> Date: Sun Mar 4 20:14:01 2012
> New Revision: 1296868
>
> URL: http://svn.apache.org/viewvc?rev=1296868&view=rev
> Log:
> * subversion/libsvn_fs_fs/rep-cache-db.sql
> (I_HASH): New index.
>
> Suggested by: Trent Nelson <trent_at_snakebite.org>
>
> Modified:
> subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql
>
> Modified: subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql
> URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql?rev=1296868&r1=1296867&r2=1296868&view=diff
> ==============================================================================
> --- subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql (original)
> +++ subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql Sun Mar 4 20:14:01 2012
> @@ -33,6 +33,11 @@ CREATE TABLE rep_cache (
> expanded_size INTEGER NOT NULL
> );
>
> +/* There isn't an implicit index on a TEXT column, so create an explicit one. */
> +CREATE INDEX I_HASH on REP_CACHE (hash);

It may be TEXT but it is also PRIMARY KEY and according to the SQLite
docs:

http://sqlite.org/lang_createtable.html

   INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY
   constraints are implemented by creating an index in the database (in
   the same way as a "CREATE UNIQUE INDEX" statement would). Such an
   index is used like any other index in the database to optimize
   queries. As a result, there often no advantage (but significant
   overhead) in creating an index on a set of columns that are already
   collectively subject to a UNIQUE or PRIMARY KEY constraint.

It appears creating the index is unnecessary and may be actively bad.

This reminds me that shortly before 1.7 I was asking about the exact
opposite of this change: whether removing existing indices from PRIMARY
KEYs, and making indices UNIQUE where possible, would be a good idea.

-- 
Philip
Received on 2012-03-06 17:41:42 CET

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