I just upgraded SQLITE to 3.7.8 from 3.7.3. I have found queries I was previously doing have become extremely slow, and wondered if its a problem with my understanding of SQL or a bug in the QUERY PLAN in newer versions: I wouldn't expect query times to change from 100ms to several minutes due to an update. If my SQL is poor then I'd love to know what is wrong with it and how I can therefore avoid making the same mistakes in future (and why it worked in 3.7.3 ok!)
The query is below along with EXPLAIN QUERY PLAN output for 3.7.8. and 3.7.3, and the re-factored query that is fast in both versions, but should result in the same query plan (I thought!) and does so in 3.7.3 ------------------------------------------------- EXPLAIN QUERY PLAN SELECT tabEmailHeaders.* from tabEmailAddress,tabRcptAddr,tabEmailHeaders,tabStore WHERE (tabEmailAddress.szAddress='a...@b.com' OR tabEmailAddress.szAddress='c...@b.com' OR tabEmailAddress.szAddress='d...@b.com' OR tabEmailAddress.szAddress='e...@b.com') AND tabRcptAddr.uidAddr=tabEmailAddress.uid AND tabEmailHeaders.uid=tabRcptAddr.uidEmail AND tabStore.uid=tabEmailHeaders.uidStore AND tabEmailHeaders.uidStore=3 AND tabEmailHeaders.nFlags=2 GROUP BY tabEmailHeaders.uid LIMIT 20 3.7.3 FAST (~100ms) 0|2|TABLE tabEmailHeaders WITH INDEX indtabEmailHeadersnFlags ORDER BY 1|3|TABLE tabStore USING PRIMARY KEY 2|1|TABLE tabRcptAddr WITH INDEX indtabRcptAddruidEmail 3|0|TABLE tabEmailAddress USING PRIMARY KEY 3.7.8 SLOW (~ minutes) 0|0|2|SEARCH TABLE tabEmailHeaders USING INDEX indtabEmailHeadersnFlags (nFlags= ?) (~2 rows) 0|1|3|SEARCH TABLE tabStore USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|0|SEARCH TABLE tabEmailAddress USING COVERING INDEX indtabEmailAddressszAddr ess (szAddress=?) (~40 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 0|3|1|SEARCH TABLE tabRcptAddr USING INDEX indtabRcptAddruidAddr (uidAddr=?) (~2 ------------------------------------------------- EXPLAIN QUERY PLAN SELECT tabEmailHeaders.* from tabRcptAddr,tabEmailHeaders,tabStore WHERE tabRcptAddr.uidAddr IN (SELECT tabEmailAddress.uid from tabEmailAddress where tabEmailAddress.szAddress='a...@b.com' OR tabEmailAddress.szAddress='c...@b.com' OR tabEmailAddress.szAddress='d...@b.com' OR tabEmailAddress.szAddress='e...@b.com') AND tabEmailHeaders.uid=tabRcptAddr.uidEmail AND tabStore.uid=tabEmailHeaders.uidStore AND tabEmailHeaders.uidStore=3 AND tabEmailHeaders.nFlags=2 GROUP BY tabEmailHeaders.uid LIMIT 20 3.7.3 FAST (~100ms) 0|1|TABLE tabEmailHeaders WITH INDEX indtabEmailHeadersnFlags ORDER BY 1|2|TABLE tabStore USING PRIMARY KEY 2|0|TABLE tabRcptAddr WITH INDEX indtabRcptAddruidEmail 0|0|TABLE tabEmailAddress WITH INDEX indtabEmailAddressszAddress 3.7.8 FAST (~100ms) 0|0|1|SEARCH TABLE tabEmailHeaders USING INDEX indtabEmailHeadersnFlags (nFlags= ?) (~2 rows) 0|1|2|SEARCH TABLE tabStore USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|0|SEARCH TABLE tabRcptAddr USING INDEX indtabRcptAddruidEmail (uidEmail=?) ( ~2 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|0|SEARCH TABLE tabEmailAddress USING COVERING INDEX indtabEmailAddressszAddr ess (szAddress=?) (~40 rows) 1|0|0|EXECUTE LIST SUBQUERY 2 ------------------------------------------------- .indices indtabEmailAddressnRefs indtabEmailAddressszAddress indtabEmailHeaderslnDate indtabEmailHeaderslnDateChanged indtabEmailHeadersnFlags indtabEmailHeadersuidFrom indtabEmailHeadersuidSndr indtabEmailHeadersuidStore indtabRcptAddruidAddr indtabRcptAddruidEmail I have tried with both Automatic index on and off. There is 128MB of cache and the DB is less than 128MB in size. Any help much appreciated. Sorry for my poor SQL if that is the issue! -- View this message in context: http://old.nabble.com/3.7.3--%3E-3.7.8-changes-tp32846314p32846314.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users