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

RE: SQL indices a WC format bump and 1.7

From: Bob Archer <Bob.Archer_at_amsi.com>
Date: Fri, 2 Sep 2011 12:21:20 -0400

> Mark Phippard wrote on Fri, Sep 02, 2011 at 12:02:02 -0400:
> > On Fri, Sep 2, 2011 at 11:42 AM, Philip Martin
> > <philip.martin_at_wandisco.com> wrote:
> > > Hyrum K Wright <hyrum.wright_at_wandisco.com> writes:
> > >
> > >>   sqlite> select * from sqlite_master where type = 'index' and
> > >> tbl_name = 'WCROOT';
> > >>   index|sqlite_autoindex_WCROOT_1|WCROOT|8|
> > >>   index|I_LOCAL_ABSPATH|WCROOT|9|CREATE UNIQUE INDEX
> > >> I_LOCAL_ABSPATH ON WCROOT (local_abspath)
> > >>
> > >> would both indicate there are two indices on the WCROOT table,
> > >> though we only define one.  I believe one of these indices is due
> > >> to the UNIQUEness of the local_abspath column.
> > >
> > > Yes, that's why I think we can delete them.  However we rarely write
> > > to these tables so the overhead of having the superfluous index is
> > > negligible.  There is no real need to remove them from 1.7 if people
> > > would prefer to make a more minimal change for 1.7.
> >
> > I do not know about SQLite, but in other databases I have worked with,
> > the database engine is intelligent enough to not create anything
> > superfluous in this sort of scenario where the index you want to
> > create already exists.

Most? Hmm.. the ones I use happily do what you tell them tool. For SQL Server I have a tool that checks for duplicate indicies for this reason.

Also, the ones I've worked with always use an index to enforce a unique constraint so making your own is redundant.

Also, someone mentioned keeping it cause you didn't add to this table much. However, I would disagree. Also, the data storage size would be doubled for no good reason.

BOb
Received on 2011-09-02 18:21:52 CEST

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