> Author: danielsh
> Date: Sun Mar 4 20:14:01 2012
> New Revision: 1296868
> URL: http://svn.apache.org/viewvc?rev=1296868&view=rev
> * 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
> 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
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.
Received on 2012-03-06 17:41:42 CET