Auto Index Warnings; key on deterministic functions

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

Auto Index Warnings; key on deterministic functions

J Decker
I have a couple tables like...

create table tableA ( pk PRIMARY KEY, dataA )
create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES tableA(pk) ON
DELETE CASCADE )

if the table was also ON UPDATE CASCADE could it slave to the same index as
primary key?

doing a LEFT JOIN on the tables  I get a warning message about automatic
index is being generated...

-------------------
Separately, can a key be added to a table that is a determinstic function?

alter tableB add column dFuncB char default dFunc(dataB)
create INDEX dFuncIndex on tableB ( dFuncB )

where dFunc is a deterministic function... oh wait, it can just be a
function... but it can't reference a column value as the source of its
data... the expression part needs to be constant.
I suppose I can add triggers to the table to update the default value.

but then that function is non-constant... so how does that index work?

------------
Kind of a X-Y problem, that is what I thought I might like to do is just
have a key into a table that's partial, because not all entries in the
table will be referenced by that key... (although since it is algorithmic,
could just be filled anyway).   But then if a could be made, the value
wouldn't actually have to exist in the table, since I really never need
that value, but just need to lookup by the value...

create INDEX dFuncIndex on tableB ( (dFunc(dataB)) )

I CAN do

select * from tableB where dFunc(dataB) === 'some value'

but that's not indexed at all.
_______________________________________________
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: Auto Index Warnings; key on deterministic functions

Keith Medcalf

That is because you do not have an index on the tableB child key of the relation (fk).  This is required.  see the lint command in a command line shell near you.

You do realize that a LEFT JOIN b is syntactic sugar for a LEFT OUTER JOIN b which means, in English, include all the rows of a whether or not there are (1 or more) matches in table b?  Therefore you require indexes on the lookup columns (unless your tables are very paltry == as in contain no rows).

Multiple on clauses can be specified as in:

create table tableB fk, datab, foreign key (fk) references tableA (pk) on delete cascade on update cascade);

You can create an index on a determinitic or slow-change function.



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of J Decker
>Sent: Monday, 5 February, 2018 18:24
>To: General Discussion of SQLite Database
>Subject: [sqlite] Auto Index Warnings; key on deterministic functions
>
>I have a couple tables like...
>
>create table tableA ( pk PRIMARY KEY, dataA )
>create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES
>tableA(pk) ON
>DELETE CASCADE )
>
>if the table was also ON UPDATE CASCADE could it slave to the same
>index as
>primary key?
>
>doing a LEFT JOIN on the tables  I get a warning message about
>automatic
>index is being generated...
>
>-------------------
>Separately, can a key be added to a table that is a determinstic
>function?
>
>alter tableB add column dFuncB char default dFunc(dataB)
>create INDEX dFuncIndex on tableB ( dFuncB )
>
>where dFunc is a deterministic function... oh wait, it can just be a
>function... but it can't reference a column value as the source of
>its
>data... the expression part needs to be constant.
>I suppose I can add triggers to the table to update the default
>value.
>
>but then that function is non-constant... so how does that index
>work?
>
>------------
>Kind of a X-Y problem, that is what I thought I might like to do is
>just
>have a key into a table that's partial, because not all entries in
>the
>table will be referenced by that key... (although since it is
>algorithmic,
>could just be filled anyway).   But then if a could be made, the
>value
>wouldn't actually have to exist in the table, since I really never
>need
>that value, but just need to lookup by the value...
>
>create INDEX dFuncIndex on tableB ( (dFunc(dataB)) )
>
>I CAN do
>
>select * from tableB where dFunc(dataB) === 'some value'
>
>but that's not indexed at all.
>_______________________________________________
>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: Auto Index Warnings; key on deterministic functions

J Decker
On Mon, Feb 5, 2018 at 6:28 PM, Keith Medcalf <[hidden email]> wrote:

>
> That is because you do not have an index on the tableB child key of the
> relation (fk).  This is required.  see the lint command in a command line
> shell near you.
>
> You do realize that a LEFT JOIN b is syntactic sugar for a LEFT OUTER JOIN
> b which means, in English, include all the rows of a whether or not there
> are (1 or more) matches in table b?  Therefore you require indexes on the
> lookup columns (unless your tables are very paltry == as in contain no
> rows).
>
> Yea;


> Multiple on clauses can be specified as in:
>
> create table tableB fk, datab, foreign key (fk) references tableA (pk) on
> delete cascade on update cascade);
>

My complaint on the index above is that it's not already just done.  being
that it is foreign, it MUST exist in PK of primary, and the FK would fault
if a PK changed without cascade updates because it now no longer references
a valid PK.

now if that weren't itself indexed of course it would need an index on the
child table then.


>
> You can create an index on a determinitic or slow-change function.
>

How? I can do...


// deterministic function
db.procedure( "hash", col=>"hash("+col+")" );
// non-determinstic function.
db.function( "myrandom", ()=>Math.random );

create table tableD ( a char );
insert into tableD( a) values (1),(2),(3),(4);
-- alter table tableD add column b char default (random());  --Error:
Cannot add a column with non-constant default
alter table tableD add column b char default (hash(a));  --Error: default
value of column [b] is not constant
select * from tableD;

create table tableE ( a char, b char default(random()) );
insert into tableE ( a) values (1),(2),(3),(4);
select * from tableE;

-- this can work if something like '12' or another function() is used
create table tableF( a char, b char default(hash(a)) ); -- Error: default
value of column [b] is not constant

create table tableF( a char, b char default(hash(random())) ); -- no error,
but wouldn't be 'constant' either...

insert into tableF ( a) values (1),(2),(3),(4);
select * from tableF;

(created with hash(random()) which is less constant than 'the value in
column a')

[ { a: 1, b: 'hash(3587505343419240000)' },
  { a: 2, b: 'hash(-578925135215998800)' },
  { a: 3, b: 'hash(-8823242962340456000)' },
  { a: 4, b: 'hash(1563125645423464400)' },
  { a: 1, b: 'hash(-593800428379214000)' },
  { a: 2, b: 'hash(-5699801957833211000)' },
  { a: 3, b: 'hash(-2110690358976664300)' },
  { a: 4, b: 'hash(-7312991265931917000)' },
  { a: 1, b: 'hash(-4664682697074342000)' },
  { a: 2, b: 'hash(5587846649139380000)' },
  { a: 3, b: 'hash(5464363349484015000)' },
  { a: 4, b: 'hash(-3159093798119682000)' } ]




but I can't (where hash is deterministic)
alter table table D add column c char default (hash(a)) )



>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of J Decker
> >Sent: Monday, 5 February, 2018 18:24
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Auto Index Warnings; key on deterministic functions
> >
> >I have a couple tables like...
> >
> >create table tableA ( pk PRIMARY KEY, dataA )
> >create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES
> >tableA(pk) ON
> >DELETE CASCADE )
> >
> >if the table was also ON UPDATE CASCADE could it slave to the same
> >index as
> >primary key?
> >
> >doing a LEFT JOIN on the tables  I get a warning message about
> >automatic
> >index is being generated...
> >
> >-------------------
> >Separately, can a key be added to a table that is a determinstic
> >function?
> >
> >alter tableB add column dFuncB char default dFunc(dataB)
> >create INDEX dFuncIndex on tableB ( dFuncB )
> >
> >where dFunc is a deterministic function... oh wait, it can just be a
> >function... but it can't reference a column value as the source of
> >its
> >data... the expression part needs to be constant.
> >I suppose I can add triggers to the table to update the default
> >value.
> >
> >but then that function is non-constant... so how does that index
> >work?
> >
> >------------
> >Kind of a X-Y problem, that is what I thought I might like to do is
> >just
> >have a key into a table that's partial, because not all entries in
> >the
> >table will be referenced by that key... (although since it is
> >algorithmic,
> >could just be filled anyway).   But then if a could be made, the
> >value
> >wouldn't actually have to exist in the table, since I really never
> >need
> >that value, but just need to lookup by the value...
> >
> >create INDEX dFuncIndex on tableB ( (dFunc(dataB)) )
> >
> >I CAN do
> >
> >select * from tableB where dFunc(dataB) === 'some value'
> >
> >but that's not indexed at all.
> >_______________________________________________
> >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
>
_______________________________________________
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: Auto Index Warnings; key on deterministic functions

Dominique Devienne
In reply to this post by J Decker
On Tue, Feb 6, 2018 at 2:24 AM, J Decker <[hidden email]> wrote:

> create table tableA ( pk PRIMARY KEY, dataA )
> create table tableB ( fk, dataB,

  FOREIGN KEY (fk) REFERENCES tableA(pk)

  ON DELETE CASCADE )
>
> if the table was also ON UPDATE CASCADE could it slave to the same index
> as primary key?
>

An index relates a value to a rowid in the associated table (or a set of
rowids if non-unique index).
So the index behind the tableA.pk column maps pk-values to tableA rowids.
While the automatic index on tableB.fk relates to tableB rowids (and is
non-unique too).
Sure the fk values are necessarily a subset of the pk values (*) for both
indexes,
but obviously the rowids are completely different, since from different
tables.
So both indexes serve different purposes, and neither can "slave" for each
other.

But perhaps I didn't understand your question correctly? Hopefully that's
useful. --DD

PS: You want an "explicit" index on tableB.fk in any case, since all FKs
should be indexed in general.
  Otherwise all your ON DELETE, ON UPDATE clauses would yields full scans
when changes
  in the parent table (tableA here) need to be propagated to child tables.
(it needs to find the child rows
  to delete or update, based on the affected tableA.pk values)

(*) if FKs are on, which they are not by default...
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users