Re: [sqlite] Curious query plan selection

[George]

On 4/16/12, Richard Hipp <d...@sqlite.org> 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