Without having seen the EXPLAIN output for the query both with and without the indexes present: the indexes you've created don't really support your query very well. Of the six indexes that you've created, I believe that only one can be used, so I'd speculate that the cause of the slowdown is the back-and-forth nature of looking up data in the index, then the table, then the index, etc. Creating a compound index of (SyntheticOutboundID, SyntheticInboundID, Season) on the YM203 table would make a huge difference in how this query will perform, I think.
-Tom > -----Original Message----- > From: John Proudlove [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 14, 2005 10:26 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Indexing problem > > 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 >
