I think you guys might be missing my point :) We know the integer primary key is an alias for the rowid - but as that table is created we also get a completely redundant index, a second b-tree, which is essentially a one to one mapping of rowids 1=1, 2=2, 3=3 etc.
The index takes up space that is not required and when updating the table and will also need to be updated when an insert etc takes places using up cycles and disk I/O. Yes Unique is redundant in the create statement, but it would be a small optimisation, unless I am missing something, for SQLite to detect this and not create the autoindex to start with. Just a thought Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 19 May 2017 at 19:29, Joseph L. Casale <jcas...@activenetwerx.com> wrote: > > -----Original Message----- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On > > Behalf Of Paul Sanderson > > Sent: Friday, May 19, 2017 12:08 PM > > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > > Subject: Re: [sqlite] auntondex with unique and integer primary key > > > I just thought it might be an area for optimisation as a redundant index > is > > built. > > According to the docs, it's only a pointer and not a duplicate when > specified exactly as 'INTEGER PRIMARY KEY'. The semantics change > when you add AUTOINCREMENT to it. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
