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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
