Daniel Shahaf wrote on Tue, Jun 07, 2011 at 21:40:00 +0300: > Bert Huijben wrote on Tue, Jun 07, 2011 at 15:09:12 +0200: > > > -----Original Message----- > > > From: danie...@apache.org [mailto:danie...@apache.org] > > > Sent: dinsdag 7 juni 2011 14:27 > > > To: comm...@subversion.apache.org > > > Subject: svn commit: r1132972 - > > > /subversion/trunk/subversion/libsvn_fs_fs/revprops-db.sql > > > > > > +++ subversion/trunk/subversion/libsvn_fs_fs/revprops-db.sql Tue Jun 7 > > > 12:27:03 2011 > > > @@ -28,7 +28,13 @@ pragma auto_vacuum = 1; > > > create table revprop (revision integer UNIQUE not null, > > > properties BLOB not null); > > > > > > -create index i_revision on revprop (revision); > > > +/* Unreleased 1.7-dev libraries also contained an index: > > > + CREATE INDEX i_revision ON revprop (revision); > > > + > > > + This was removed since the UNIQUE statement already constructs > > > + its own index. > > > + */ > > > + > > > > Shouldn't revision be the primary key instead of just unique? > > > > IIRC there is no functional difference in the case, but I agree that > spelling out PRIMARY KEY will be better, will look into that.
This time the EXPLAIN's show a significant difference, which to my untrained eye seems to be in favour of PRIMARY KEY. (They're attached.) I've made the change in r1133237 (and asked a question in the commit), but if someone has concerns please voice them.
EXPLAIN STMT_SET_REVPROP |Trace|0|0|0||00| |Goto|0|15|0||00| |OpenWrite|0|3|0|2|00| |Integer|1|2|0||00| |NotNull|2|6|0||00| |NewRowid|0|2|0||00| |MustBeInt|2|0|0||00| |Null|0|3|0||00| |String8|0|4|0|( foo bar ) |00| |HaltIfNull|19|2|4|revprop.properties may not be NULL|00| |NotExists|0|11|2||00| |MakeRecord|3|2|5|db|00| |Insert|0|5|2|revprop|03| |Close|0|0|0||00| |Halt|0|0|0||00| |Transaction|0|1|0||00| |VerifyCookie|0|1|0||00| |TableLock|0|3|1|revprop|00| |Goto|0|2|0||00| EXPLAIN STMT_GET_REVPROP |Trace|0|0|0||00| |Variable|1|1|0|?1|00| |Goto|0|10|0||00| |OpenRead|0|3|0|2|00| |MustBeInt|1|8|0||00| |NotExists|0|8|1||00| |Column|0|1|3||00| |ResultRow|3|1|0||00| |Close|0|0|0||00| |Halt|0|0|0||00| |Transaction|0|0|0||00| |VerifyCookie|0|1|0||00| |TableLock|0|3|0|revprop|00| |Goto|0|3|0||00|
EXPLAIN STMT_SET_REVPROP |Trace|0|0|0||00| |Goto|0|25|0||00| |OpenWrite|0|3|0|2|00| |OpenWrite|1|4|0|keyinfo(1,BINARY)|00| |NewRowid|0|3|0||00| |Integer|1|4|0||00| |String8|0|5|0|( foo bar ) |00| |HaltIfNull|19|2|4|revprop.revision may not be NULL|00| |HaltIfNull|19|2|5|revprop.properties may not be NULL|00| |SCopy|4|6|0||00| |SCopy|3|7|0||00| |MakeRecord|6|2|1|db|00| |SCopy|3|8|0||00| |IsUnique|1|19|8|6|00| |NotExists|0|19|8||00| |Rowid|0|7|0||00| |Column|0|0|6||00| |IdxDelete|1|6|2||00| |Delete|0|0|0||00| |IdxInsert|1|1|0||00| |MakeRecord|4|2|8|db|00| |Insert|0|8|3|revprop|0b| |Close|0|0|0||00| |Close|1|0|0||00| |Halt|0|0|0||00| |Transaction|0|1|0||00| |VerifyCookie|0|1|0||00| |TableLock|0|3|1|revprop|00| |Goto|0|2|0||00| EXPLAIN STMT_GET_REVPROP |Trace|0|0|0||00| |Variable|1|1|0|?1|00| |Goto|0|17|0||00| |OpenRead|0|3|0|2|00| |OpenRead|1|4|0|keyinfo(1,BINARY)|00| |IsNull|1|14|0||00| |Affinity|1|1|0|d|00| |SeekGe|1|14|1|1|00| |IdxGE|1|14|1|1|01| |IdxRowid|1|2|0||00| |Seek|0|2|0||00| |Column|0|1|3||00| |ResultRow|3|1|0||00| |Next|1|8|0||00| |Close|0|0|0||00| |Close|1|0|0||00| |Halt|0|0|0||00| |Transaction|0|0|0||00| |VerifyCookie|0|1|0||00| |TableLock|0|3|0|revprop|00| |Goto|0|3|0||00|
