Quantcast

Another Inconsistency in .lint fkeys-index

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

Another Inconsistency in .lint fkeys-index

Keith Medcalf

Note the report first line.  Interval is the primary key of both tables so
there is an internal index.
Table Forecast does the same but is not reported (because of the extra
unique constraints perhaps?)

The later two reported missing indexes are correct.

sqlite> .lint fkey-indexes
CREATE INDEX 'Actual_Interval' ON 'Actual'('Interval'); -->
Interval(Interval)
CREATE INDEX 'Generator_SrcType' ON 'Generator'('SrcType'); -->
Source(SrcType)
CREATE INDEX 'GeneratorSummary_Unit' ON 'GeneratorSummary'('Unit'); -->
Generator(Unit)

sqlite> .schema
CREATE TABLE Interval
(
    esoYear     integer not null check(esoYear between 1970 and 2038),
    esoMonth    integer not null check(esoMonth between 1 and 12),
    esoDay      integer not null check(esoDay between 1 and 31),
    esoHour     text collate nocase not null,
    Interval    integer primary key,
    unique (esoYear, esoMonth, esoDay, esoHour)
);
CREATE TABLE Forecast
(
    Interval    integer not null references Interval (Interval),
    Projected   integer not null references Interval (Interval)
check((Interval - Projected) between 0 and 2),
    UTCUpdate   integer,
    Price       float,
    Demand      integer,
    unique (Interval, Projected),
    unique (Projected, Interval)
);
CREATE TABLE Actual
(
    Interval    integer primary key references Interval (Interval),
    UTCUpdate   integer,
    Price       float,
    Demand      integer
);

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

Re: Another Inconsistency in .lint fkeys-index

Dan Kennedy-4
On 04/06/2017 08:26 PM, Keith Medcalf wrote:
> Note the report first line.  Interval is the primary key of both tables so
> there is an internal index.

Thanks for this. Should be fixed here:

   http://www.sqlite.org/src/info/48826b222c110a90

Dan.


> Table Forecast does the same but is not reported (because of the extra
> unique constraints perhaps?)
>
> The later two reported missing indexes are correct.
>
> sqlite> .lint fkey-indexes
> CREATE INDEX 'Actual_Interval' ON 'Actual'('Interval'); -->
> Interval(Interval)
> CREATE INDEX 'Generator_SrcType' ON 'Generator'('SrcType'); -->
> Source(SrcType)
> CREATE INDEX 'GeneratorSummary_Unit' ON 'GeneratorSummary'('Unit'); -->
> Generator(Unit)
>
> sqlite> .schema
> CREATE TABLE Interval
> (
>      esoYear     integer not null check(esoYear between 1970 and 2038),
>      esoMonth    integer not null check(esoMonth between 1 and 12),
>      esoDay      integer not null check(esoDay between 1 and 31),
>      esoHour     text collate nocase not null,
>      Interval    integer primary key,
>      unique (esoYear, esoMonth, esoDay, esoHour)
> );
> CREATE TABLE Forecast
> (
>      Interval    integer not null references Interval (Interval),
>      Projected   integer not null references Interval (Interval)
> check((Interval - Projected) between 0 and 2),
>      UTCUpdate   integer,
>      Price       float,
>      Demand      integer,
>      unique (Interval, Projected),
>      unique (Projected, Interval)
> );
> CREATE TABLE Actual
> (
>      Interval    integer primary key references Interval (Interval),
>      UTCUpdate   integer,
>      Price       float,
>      Demand      integer
> );
>
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: Another Inconsistency in .lint fkeys-index

David Raymond
That fixed the issue I was seeing the first time around, thanks.

Still getting some weirdness where it looks like the results are highly dependent on the contents of sqlite_stat1. I've been trying to construct a simplified version to share but am having trouble reproducing it on a smaller scale than the oversize, overcomplicated db I picked to test with.

F:\Data\Temp\Weirdness>sqlite3 orig.sqlite
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.

sqlite> select sqlite_version();
sqlite_version()
3.18.0
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> select sqlite_source_id();
sqlite_source_id()
2017-03-28 18:48:43 424a0d380332858ee55bdebc4af3789f74e70a2b3ba1cf29d84b9b4bcf3e2e37
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> .lint fkey-indexes
CREATE INDEX foo --details omitted to protect the innocent... and I guess even the guilty
CREATE INDEX bar

sqlite> .exit

F:\Data\Temp\Weirdness>sqlite3 sameSchema.sqlite
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.

sqlite> .read schema.sql --same as what the original came from

sqlite> .lint fkey-indexes

sqlite> analyze sqlite_master;
Run Time: real 0.546 user 0.000000 sys 0.000000

sqlite> .lint fkey-indexes

sqlite> attach database 'orig.sqlite' as orig;
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> insert into main.sqlite_stat1 select * from orig.sqlite_stat1;
Run Time: real 0.546 user 0.000000 sys 0.000000

sqlite> detach database orig;
Run Time: real 0.093 user 0.000000 sys 0.000000

sqlite> .lint fkey-indexes

sqlite> analyze sqlite_master;
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> .lint fkey-indexes
CREATE INDEX foo --details omitted to protect the innocent
CREATE INDEX bar

sqlite> delete from sqlite_stat1;
Run Time: real 0.468 user 0.000000 sys 0.015600

sqlite> .lint fkey-indexes
CREATE INDEX foo --details omitted to protect the innocent
CREATE INDEX bar

sqlite> analyze sqlite_master;
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> .lint fkey-indexes
CREATE INDEX foo --details omitted to protect the innocent
CREATE INDEX bar
CREATE INDEX baz
CREATE INDEX qux
...
--13 total create index suggestions produced

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

Re: Another Inconsistency in .lint fkeys-index

Keith Medcalf
In reply to this post by Dan Kennedy-4
On Thursday, 6 April, 2017 08:58, Dan Kennedy <[hidden email]> wrote:

> On 04/06/2017 08:26 PM, Keith Medcalf wrote:

> > Note the report first line.  Interval is the primary key of both tables
> > so there is an internal index.
 
> Thanks for this. Should be fixed here:
 
>    http://www.sqlite.org/src/info/48826b222c110a90

Thanks Dan, fix confirmed.

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