[sqlite] Indexing problem

[John Proudlove]

Hello,

Can anyone shed light on the following problem,
experienced with the SQLite command line utility
(v3.0.8) on Solaris/SPARC?

The query below hangs (fails to complete within 5
minutes) using the indices shown, but after removing
the index on the Season column (used in the WHERE
condition), it completes within seconds.

Regards,
John

SELECT
    count(*)
FROM
    YM207, YM203
WHERE
        YM203.Season = "W2004"
    AND YM207.OutboundFlightSeriesID
            = YM203.SyntheticOutboundFlightSeriesID
    AND YM207.InboundFlightSeriesID
            = YM203.SyntheticInboundFlightSeriesID
;

CREATE INDEX YM203_SSID ON YM203 ( SyntheticSellingID
);
CREATE UNIQUE INDEX YM203_index ON YM203 (
SyntheticPackageID );
CREATE INDEX YM203_index_2 ON YM203 (
SyntheticOutboundFlightSeriesID );
CREATE INDEX YM203_index_3 ON YM203 (
SyntheticInboundFlightSeriesID );
CREATE INDEX YM203_index_5 ON YM203 ( SubProductCode
);
CREATE INDEX YM203_index_4 ON YM203 ( Season );


Send instant messages to your online friends http://uk.messenger.yahoo.com