danie...@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 <tr...@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
