Tom, Many thanks for the suggestion of building a compound index, it worked like a dose of salts.
I was puzzled that removing the single-column index on Season actually enabled the original query to complete, having read somewhere in the sqlite docs that indexing columns used in WHERE conditions improves performance. Is this something to do with the Season column having low cardinality (selectivity) in my data? In case you are able to throw more light on this I am including the explain output below. Regards, John addr opcode p1 p2 p3 ---- ------------ ---------- ---------- ----------------------------------- 0 AggReset 1 1 1 AggInit 0 0 count(0) 2 Goto 0 44 3 Integer 0 0 4 OpenRead 0 84118 5 SetNumColumn 0 10 6 Integer 0 0 7 OpenRead 1 9 8 SetNumColumn 1 13 9 Integer 0 0 10 OpenRead 2 1756346 keyinfo(1,BINARY) 11 Rewind 0 35 12 String8 0 0 W2004 13 NotNull -1 16 14 Pop 1 0 15 Goto 0 34 16 MakeRecord 1 0 t 17 MemStore 0 0 18 MoveGe 2 34 19 MemLoad 0 0 20 IdxGE 2 34 + 21 RowKey 2 0 22 IdxIsNull 1 33 23 IdxRecno 2 0 24 MoveGe 1 0 25 Column 1 5 26 Column 0 0 27 Ne 28161 33 collseq(BINARY) 28 Column 1 6 29 Column 0 2 30 Ne 28161 33 collseq(BINARY) 31 Integer 0 0 32 AggFunc 0 0 ptr(0x2f540) 33 Next 2 19 34 Next 0 12 35 Close 0 0 36 Close 1 0 37 Close 2 0 38 AggNext 0 42 39 AggGet 0 0 40 Callback 1 0 41 Goto 0 38 42 Noop 0 0 43 Halt 0 0 44 Transaction 0 0 45 VerifyCookie 0 214 46 Goto 0 3 47 Noop 0 0 Thomas Briggs Thu, 14 Apr 2005 07:42:03 -0700 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
