Ian Hardingham <i...@omroth.com> wrote: > I was attempting to optimise this query this weekend: > > SELECT * FROM multiturnTable WHERE (player1 LIKE '?' OR player2 LIKE > '?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND > p1Declined=0 AND p2Declined=0; > > What I'll be trying next is to put an index on player1 and player2. Am > I right in thinking I only need an index on those individually - I don't > need to combine them with id or some such?
Try this: SELECT * FROM multiturnTable WHERE rowid in ( select rowid from multiturnTable where player1 LIKE ? union all select rowid from multiturnTable where player2 LIKE ? ) AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0) AND p1Declined=0 AND p2Declined=0; With this, indexes on player1 and player2 (separately) should help. With the original query, they are unlikely to help. Note also that '?' is a string literal consisting of one question mark character. It is *not* a parameter placeholder. ? is a placeholder for a parameter of any type (not just integer, as you seem to assume) - the type is determined by the flavor of sqlite3_bind_* function you are using to set its value. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
