auntondex with unique and integer primary key

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

auntondex with unique and integer primary key

Paul Sanderson
Is the autoindex associated when using unique with an integer primary key
definition redundant?

I have seen a number of DBs/tables created in the following form:

Create table test(id integer unique primary key);

Insert into test values (1);

Insert into test values (2);

Insert into test values (3);

The table is created and populated as expected, but an
sqlite_autoindex_test_1 is also created with content that mirrors exactly
the rowid/id.

Is the autoindex redundant and is this an opportunity for optimisation?


Using 3.18.0

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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Joseph L. Casale
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On
> Behalf Of Paul Sanderson
> Sent: Friday, May 19, 2017 11:22 AM
> To: General Discussion of SQLite Database <sqlite-
> [hidden email]>
> Subject: [sqlite] auntondex with unique and integer primary key

> Is the autoindex redundant and is this an opportunity for optimisation?

See https://sqlite.org/autoinc.html and https://www.sqlite.org/rowidtable.html
for the nuances and rational for one versus the other.

Hth,
jlc
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Simon Slavin-3
In reply to this post by Paul Sanderson

On 19 May 2017, at 6:21pm, Paul Sanderson <[hidden email]> wrote:

> Is the autoindex associated when using unique with an integer primary key
> definition redundant?
>
> I have seen a number of DBs/tables created in the following form:
>
> Create table test(id integer unique primary key);
> Insert into test values (1);
> Insert into test values (2);
> Insert into test values (3);
>
> The table is created and populated as expected, but an
> sqlite_autoindex_test_1 is also created with content that mirrors exactly
> the rowid/id.
>
> Is the autoindex redundant and is this an opportunity for optimisation?

There’s no point in declaring the primary key as unique.  A primary key has to be unique.  SQLite will enforce uniqueness whether you tell it to or not.

sqlite> Create table test1(id integer primary key);
sqlite> Create table test2(id integer unique primary key);
sqlite> Create table test3(id integer primary key unique);
sqlite> PRAGMA index_list(test1);
sqlite> PRAGMA index_list(test2);
0|sqlite_autoindex_test2_1|1|u|0
sqlite> PRAGMA index_list(test3);
0|sqlite_autoindex_test3_1|1|u|0
sqlite>

It appears that SQLite does not notice that you have declared a primary key as unique.  It’s really this that’s causing the problem.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

J. King-3
In reply to this post by Paul Sanderson
On May 19, 2017 1:21:49 PM EDT, Paul Sanderson <[hidden email]> wrote:

>Is the autoindex associated when using unique with an integer primary
>key
>definition redundant?
>
>I have seen a number of DBs/tables created in the following form:
>
>Create table test(id integer unique primary key);
>
>Insert into test values (1);
>
>Insert into test values (2);
>
>Insert into test values (3);
>
>The table is created and populated as expected, but an
>sqlite_autoindex_test_1 is also created with content that mirrors
>exactly
>the rowid/id.
>
>Is the autoindex redundant and is this an opportunity for optimisation?
>
>
>Using 3.18.0
>
>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
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

A primary key is by definition unique, so the unique constraint is needless duplication. Note, too, that the primary key is only an alias for the rowid when using the formulation "integer primary key" exactly.
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Paul Sanderson
In reply to this post by Simon Slavin-3
Thanks Simon

I am aware that a PK must be unique :)

It's not me that's declaring it as unique - I get to look at thousands of
databases that other people create and it is these where I have noticed it
(Chrome and Skype are two).

I just thought it might be an area for optimisation as a redundant index is
built.




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 18:49, Simon Slavin <[hidden email]> wrote:

>
> On 19 May 2017, at 6:21pm, Paul Sanderson <[hidden email]>
> wrote:
>
> > Is the autoindex associated when using unique with an integer primary key
> > definition redundant?
> >
> > I have seen a number of DBs/tables created in the following form:
> >
> > Create table test(id integer unique primary key);
> > Insert into test values (1);
> > Insert into test values (2);
> > Insert into test values (3);
> >
> > The table is created and populated as expected, but an
> > sqlite_autoindex_test_1 is also created with content that mirrors exactly
> > the rowid/id.
> >
> > Is the autoindex redundant and is this an opportunity for optimisation?
>
> There’s no point in declaring the primary key as unique.  A primary key
> has to be unique.  SQLite will enforce uniqueness whether you tell it to or
> not.
>
> sqlite> Create table test1(id integer primary key);
> sqlite> Create table test2(id integer unique primary key);
> sqlite> Create table test3(id integer primary key unique);
> sqlite> PRAGMA index_list(test1);
> sqlite> PRAGMA index_list(test2);
> 0|sqlite_autoindex_test2_1|1|u|0
> sqlite> PRAGMA index_list(test3);
> 0|sqlite_autoindex_test3_1|1|u|0
> sqlite>
>
> It appears that SQLite does not notice that you have declared a primary
> key as unique.  It’s really this that’s causing the problem.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Joseph L. Casale
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On
> Behalf Of Paul Sanderson
> Sent: Friday, May 19, 2017 12:08 PM
> To: SQLite mailing list <[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Paul Sanderson
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 <[hidden email]> wrote:

> > -----Original Message-----
> > From: sqlite-users [mailto:[hidden email]]
> On
> > Behalf Of Paul Sanderson
> > Sent: Friday, May 19, 2017 12:08 PM
> > To: SQLite mailing list <[hidden email]>
> > 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Richard Hipp-3
On 5/19/17, Paul Sanderson <[hidden email]> wrote:
>
> 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.
>

That would be great, if we had caught the problem 15 years ago.  But
now, we have to keep the databases backwards compatible, and omitting
the index would generate an incompatible database file.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Keith Medcalf
In reply to this post by Paul Sanderson

No.  

You asked for the extra index to be created in the table specification.  It is not the job of the database engine to correct your errors (it is not even possible to know if it is an error).  

If you declared that you want an extra UNIQUE index on an INTEGER PRIMARY KEY (which is already unique), then how is the software to know that you do not know what you are doing -vs- that you DO know what you are doing and need that extra UNIQUE index?  

Perhaps there is a reason that you want a separate unique index.  Maybe the table has 15000 columns and from time to time you just need to be able to scan the used RowIDs without incurring the penalty of wafting to and fro all over the disk.

So, the real optimization opportunity rests with the database designer who should not create non-required indexes.  For example, one may declare:

create table x(rowid unique integer primary key, value text not null collate nocase unique);
create index pk_x on x as (rowid);
create index fk_x on x as (value);
create unique index ak_x on x (value);

Although you have declared a multiplicity of redundant indexes, there is no way to tell whether you did so because (a) you intended to do so for some particular reason (ie, requirement); or, (b) you simply do not know what you are doing (ie, made an error).  Computer software is supposed to do what it is told to do.  Exactly.  With no arguments and without interpretation of meaning.  And if it does not understand, then it should spit up one of the most useful error messages ever devised:  "Who?", "What?", "Where?"; and, "How?"

One of the very few exceptions to this is the PL/1 Level F Optimizing Compilers.  If you sputter nonsence it will try to put a "meaningful spin" on your sputterings -- one of the very first implementations of "Plug and Pray" (and, unless you really knew what you were doing, working with about the same success).

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Paul Sanderson
> Sent: Friday, 19 May, 2017 11:22
> To: General Discussion of SQLite Database
> Subject: [sqlite] auntondex with unique and integer primary key
>
> Is the autoindex associated when using unique with an integer primary key
> definition redundant?
>
> I have seen a number of DBs/tables created in the following form:
>
> Create table test(id integer unique primary key);
>
> Insert into test values (1);
>
> Insert into test values (2);
>
> Insert into test values (3);
>
> The table is created and populated as expected, but an
> sqlite_autoindex_test_1 is also created with content that mirrors exactly
> the rowid/id.
>
> Is the autoindex redundant and is this an opportunity for optimisation?
>
>
> Using 3.18.0
>
> 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
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Paul Sanderson
Ahh -

I always let SQLite decide what index to use as I assume that it knows
best. I have never used "indexed by" to force the use of a specific index -
I see the issue with backward compatibility now.

Thanks Richard

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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

David Raymond
In reply to this post by Keith Medcalf
This is the only reason I can think of for the redundancy and was actually thinking of it earlier.

Consider the basic "select count(*) from ...;" If you've got a lot of fields, or if they're large fields, then the fanout of your records means you may have to get a whole bunch of pages to find how many records you have. An index on just the rowid is as compact as possible and would provide the fastest possible answer to the basic count query.

Hmm, it would also provide quick checking of foreign keys if it's a parent table. Might also be useful in some joins as a quick way to see if there's a match etc.

So yeah, there are some possible reasons for it. The "unique" bit is redundant, but if you want that extra index for one of the above mentioned reasons, then it means you can create it right there for 1 extra word. Of course without a comment in the create statement no one's gonna understand why you put it there, and everything will function just fine without it.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Keith Medcalf
Sent: Friday, May 19, 2017 3:06 PM
To: SQLite mailing list
Subject: Re: [sqlite] auntondex with unique and integer primary key


Perhaps there is a reason that you want a separate unique index.  Maybe the table has 15000 columns and from time to time you just need to be able to scan the used RowIDs without incurring the penalty of wafting to and fro all over the disk.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

James K. Lowden
In reply to this post by Keith Medcalf
On Fri, 19 May 2017 13:06:23 -0600
"Keith Medcalf" <[hidden email]> wrote:

> You asked for the extra index to be created in the table
> specification.  It is not the job of the database engine to correct
> your errors (it is not even possible to know if it is an error).  

He didn't ask.  It's not an error.  And it is within the purview of the
DBMS to make any implementation decision it wishes to effect the
described outcome.  Other than that, we agree!  

Nothing about any SQL statement implies anything about the
implementation.  Thus, as you know, a unique constraint is not an
instruction to build an index, much less a requirement to build a
redundant one. It's a rule. Carry it out as ye may.  

Redundant constraints are not errors.  They're not wrong in any sense.
They're not minimal, that's all.  The logical redundancy can be
ascertained by the system, and there the redundancy can be excised.
Nothing forces the DBMS to check twice something that need be checked
only once.  

I appreciate that there are other constraints on the system, such as
backwards compatibility and feasibilty.  That's fine.  But let's not
gussy up pragmatic choices or problematic history as logically
mandated.  If we do, we're apt to miss opportunities to make
improvements, and teach users the wrong lessons about what to do.  

--jkl


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: auntondex with unique and integer primary key

Simon Slavin-3

On 26 May 2017, at 2:47am, James K. Lowden <[hidden email]> wrote:

> Nothing about any SQL statement implies anything about the
> implementation.  Thus, as you know, a unique constraint is not an
> instruction to build an index, much less a requirement to build a
> redundant one. It's a rule. Carry it out as ye may.

The other side of this is that the SQL engine can make up indexes if it wants to.  And it can delete those indexes (though not indexes you asked for) if it wants to.  Automatic indexes are a detail of how the SQL engine does its job and not the programmer's responsibility.

So yes, OP spotted an unnecessary index in the cited case.  Whether it’s worth spotting the combination of things that lead to it is not settled.  Checking for the redundancy of "PRIMARY KEY UNIQUE" may slow down every CREATE TABLE command whereas this redundent index is created only when the programmer asks for it.  It may not be worth changing the way SQLite works now.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users