Re: [sqlite] Curious query plan selection

[Richard Hipp]

On Mon, Apr 16, 2012 at 10:31 AM, George  wrote:

> On 4/16/12, Richard Hipp  wrote:
> > On Mon, Apr 16, 2012 at 9:43 AM, George  wrote:
> >
> >>
> >> create table if not exists SnapshotsMarketsRunners(
> >>ID integer primary key,
> >>SnapshotsID integer not null references Snapshots(ID),
> >>MarketsRunnersID not null references MarketsRunners(ID),
> >>
> >
> > Specify type "integer" on the MarketRunnersID column
> >
>
> Thanks, it works now. I had omitted the type name by mistake. I assume
> that the absence of data type specification means that the column can
> accept any data, is this correct?
>

Correct.  That means that you could have inserted a string '123' instead of
a number 123, and the string form would have been retained.  And because of
that, the index could not bee used to do the lookup.


> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious query plan selection

[George]

On 4/16/12, Richard Hipp  wrote:
> On Mon, Apr 16, 2012 at 9:43 AM, George  wrote:
>
>>
>> create table if not exists SnapshotsMarketsRunners(
>>ID integer primary key,
>>SnapshotsID integer not null references Snapshots(ID),
>>MarketsRunnersID not null references MarketsRunners(ID),
>>
>
> Specify type "integer" on the MarketRunnersID column
>

Thanks, it works now. I had omitted the type name by mistake. I assume
that the absence of data type specification means that the column can
accept any data, is this correct?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious query plan selection

[Richard Hipp]

On Mon, Apr 16, 2012 at 9:43 AM, George  wrote:

>
> create table if not exists SnapshotsMarketsRunners(
>ID integer primary key,
>SnapshotsID integer not null references Snapshots(ID),
>MarketsRunnersID not null references MarketsRunners(ID),
>

Specify type "integer" on the MarketRunnersID column



>LastPriceMatched real
> );
>

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious query plan selection

[George]

On 4/16/12, Richard Hipp  wrote:

> Hard to say why, without knowing your schema.

Here is the schema:

create table if not exists Errors(
ID integer primary key,
Timestamp text not null,
ErrorCode text,
Name text,
RequestName text,
Response text,
ErrorText text
);

create table if not exists Markets(
ID integer primary key,
StartTimestamp text not null,
Name text not null,
RecordingPID text unique,
RecorderInterval real check (RecorderInterval >= 0.05),
IsDone integer not null check(IsDone in (0,1))
);

create table if not exists Runners(
ID integer primary key
);

create table if not exists MarketsRunners(
ID integer primary key,
RunnersID integer not null references Runners(ID),
MarketsID integer not null references Markets(ID)
);

create table if not exists Snapshots(
ID integer primary key,
Timestamp text not null
);

create table if not exists SnapshotsMarketsRunners(
ID integer primary key,
SnapshotsID integer not null references Snapshots(ID),
MarketsRunnersID not null references MarketsRunners(ID),
LastPriceMatched real
);

create table if not exists AvailablePrices(
ID integer primary key,
Price real not null check (Price > 1.),
VolumeAvailable real not null check (VolumeAvailable > 0),
BackOrLay text check (BackOrLay in ('B', 'L')),
SnapshotsMarketsRunnersID integer not null references
SnapshotsMarketsRunners(ID)
);

create unique index if not exists MarketsRunnersRunnersIDMarketsID on
MarketsRunners(RunnersID, MarketsID);

create index if not exists MarketsRunnersMarketsID on MarketsRunners(MarketsID);

create index if not exists AvailablePricesSnapshotsMarketsRunnersID on
AvailablePrices(SnapshotsMarketsRunnersID);

create index if not exists SnapshotsTimestamp on Snapshots(Timestamp);

create index if not exists
AvailablePricesSnapshotsMarketsRunnersIDBackOrLayPrice1 on
AvailablePrices(SnapshotsMarketsRunnersID, BackOrLay, Price);

create index if not exists
AvailablePricesSnapshotsMarketsRunnersIDBackOrLayPrice2 on
AvailablePrices(SnapshotsMarketsRunnersID, BackOrLay desc, Price
desc);

create index if not exists
AvailablePricesSnapshotsMarketsRunnersIDBackOrLayPrice3 on
AvailablePrices(SnapshotsMarketsRunnersID, BackOrLay, Price desc);

create index if not exists
AvailablePricesSnapshotsMarketsRunnersIDBackOrLayPrice4 on
AvailablePrices(SnapshotsMarketsRunnersID, BackOrLay desc, Price);

create index if not exists SnapshotsIDTimestamp on Snapshots(ID, Timestamp);

create unique index if not exists
SnapshotsMarketsRunnersSnapshotsIDMarketsRunnersID on
SnapshotsMarketsRunners(SnapshotsID, MarketsRunnersID);

create unique index if not exists
SnapshotsMarketsRunnersMarketsRunnersIDSnapshotsID on
SnapshotsMarketsRunners(MarketsRunnersID, SnapshotsID);

create unique index if not exists
AvailablePricesUniquePriceSnapshotsMarketsRunnersID on
AvailablePrices(Price, SnapshotsMarketsRunnersID);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious query plan selection

[Richard Hipp]

On Sun, Apr 15, 2012 at 2:31 PM, George  wrote:

> Compare the following two queries and their query plans:
>
> 1) explain query plan select * from snapshotsmarketsrunners where marketsru
> nnersid in (1);
>
> 0|0|0|SEARCH TABLE snapshotsmarketsrunners USING INDEX
> SnapshotsMarketsRunnersMa
> rketsRunnersIDSnapshotsID (MarketsRunnersID=?) (~10 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
> 2) explain query plan select * from snapshotsmarketsrunners where marketsru
> nnersid in (select id from marketsrunners where marketsid = 105195390);
>
> 0|0|0|SCAN TABLE snapshotsmarketsrunners (~223868 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 1|0|0|SEARCH TABLE marketsrunners USING COVERING INDEX
> MarketsRunnersMarketsID (
> MarketsID=?) (~10 rows)
>
> Why is the index used in the first query but not in the second?


Hard to say why, without knowing your schema.



> The
> same thing happens if I rewrite query (2) using JOIN syntax:
>
> explain query plan select * from snapshotsmarketsrunners join marketsrun
> ners on marketsrunnersid = marketsrunners.id where marketsid = 105195390;
>
> 0|0|0|SCAN TABLE snapshotsmarketsrunners (~2238685 rows)
> 0|1|1|SEARCH TABLE marketsrunners USING INTEGER PRIMARY KEY (rowid=?) (~1
> rows)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Curious query plan selection

[George]

Compare the following two queries and their query plans:

1) explain query plan select * from snapshotsmarketsrunners where marketsru
nnersid in (1);

0|0|0|SEARCH TABLE snapshotsmarketsrunners USING INDEX SnapshotsMarketsRunnersMa
rketsRunnersIDSnapshotsID (MarketsRunnersID=?) (~10 rows)
0|0|0|EXECUTE LIST SUBQUERY 1

2) explain query plan select * from snapshotsmarketsrunners where marketsru
nnersid in (select id from marketsrunners where marketsid = 105195390);

0|0|0|SCAN TABLE snapshotsmarketsrunners (~223868 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SEARCH TABLE marketsrunners USING COVERING INDEX MarketsRunnersMarketsID (
MarketsID=?) (~10 rows)

Why is the index used in the first query but not in the second? The
same thing happens if I rewrite query (2) using JOIN syntax:

explain query plan select * from snapshotsmarketsrunners join marketsrun
ners on marketsrunnersid = marketsrunners.id where marketsid = 105195390;

0|0|0|SCAN TABLE snapshotsmarketsrunners (~2238685 rows)
0|1|1|SEARCH TABLE marketsrunners USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users