Re: [sqlite] auntondex with unique and integer primary key

[Simon Slavin]

On 26 May 2017, at 2:47am, James K. Lowden  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[James K. Lowden]

On Fri, 19 May 2017 13:06:23 -0600
"Keith Medcalf"  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[David Raymond]

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:sqlite-users-boun...@mailinglists.sqlite.org] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[Keith Medcalf]

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:sqlite-users-boun...@mailinglists.sqlite.org]
> 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
> 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

Re: [sqlite] auntondex with unique and integer primary key

[Richard Hipp]

On 5/19/17, Paul Sanderson  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <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

Re: [sqlite] auntondex with unique and integer primary key

[Joseph L. Casale]

> -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

Re: [sqlite] auntondex with unique and integer primary key

[Paul Sanderson]

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  wrote:

>
> On 19 May 2017, at 6:21pm, Paul Sanderson 
> 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
> 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

Re: [sqlite] auntondex with unique and integer primary key

[J. King]

On May 19, 2017 1:21:49 PM EDT, Paul Sanderson  
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
>sqlite-users@mailinglists.sqlite.org
>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[Simon Slavin]

On 19 May 2017, at 6:21pm, Paul Sanderson  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[Joseph L. Casale]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Paul Sanderson
> Sent: Friday, May 19, 2017 11:22 AM
> To: General Discussion of SQLite Database  us...@mailinglists.sqlite.org>
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users