> My tests show that, counter-intuitively, the second query takes between > 3 and 5 times as long as the first query. > > This seems weird to me - any ideas why this would be?
What does EXPLAIN QUERY PLAN for both queries say? And what SQLite version do you use? Also do you understand that '?' (exactly like that in quotes) is not a parameter you can bind? It's a string consisting of one character. Pavel On Tue, Mar 15, 2011 at 7:37 AM, Ian Hardingham <[email protected]> wrote: > Hey guys. > > Due to some help I had yesterday, I was advised to change this query: > > 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) > > To this one: > > SELECT * FROM (SELECT * FROM multiturnTable ORDER BY id DESC LIMIT 5000) > WHERE (player1 = '?' or player2 = '?') AND (complete=0 or > p1SubmitScore=0 or p2SubmitScore=0) > > (Information about multiturnTable is at the end of this email). > > My tests show that, counter-intuitively, the second query takes between > 3 and 5 times as long as the first query. > > This seems weird to me - any ideas why this would be? > > Thanks, > Ian > > Further info: > > multiturnTable has 70,000 rows, currently has no indices, and is created > like this: > > CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT > NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE ... > complete INTEGER ... p1SubmitScore INTEGER, p2SubmitScore INTEGER ... > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
