Not sure why you are using a subselect with a union all. Try this:
SELECT * FROM multiturnTable WHERE (player1 ='?' OR player2 ='?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 > > Message: 1 > Date: Sun, 13 Mar 2011 11:43:30 +0000 > From: Ian Hardingham <i...@omroth.com> > Subject: [sqlite] Optimising a query with several criteria > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Message-ID: <4d7cade2.5050...@omroth.com> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Hey guys. > > I've optimised most of my queries to work effectively, but I have one > which is sometimes causing me problems. It is: > > SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM > multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM > multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 > OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0 > > multiturnTable has about 100,000 rows. > > (My apologies if I keep harping on about this same general area). > > This query needs to run only once per client session - which isn't very > often. However, when the server is taking up a lot of RAM I've seen > this query take 30 seconds. When there's plenty of RAM it only takes in > the region of 100ms. Does anyone have any advice? > > Thanks, > Ian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
