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;
multiturnTable has about 70,000 rows and has no explicit indexes. I was calling with arguments which produce around 8 results. The first time the query runs on starting my application it takes around 120ms (as reported by SQlite_profile). Consecutive runs vary between 80 and 100. The first thing I did was simply change the LIKE to =, like so: SELECT * FROM multiturnTable WHERE (player1 = '?' OR player2 = '?') AND ... This, surprisingly to me, had no real effect on the run time of the query. So I changed player1 and player2 to be integers, like this: SELECT * FROM multiturnTable WHERE (player1Id = ? OR player2Id = ?) AND ... I really expected this to improve the run time, but it didn't. Maybe a 5% improvement. 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? Thanks, Ian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
