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