Hi, ALL, Now that the loop in the query is fixed I want to come back to this topic.
Here is modified query plan: sqlite> EXPLAIN QUERY PLAN SELECT playersinleague.playerid,scorehits.scorename,l eaguescorehitter.value FROM playersinleague,scorehits,leaguescorehitter WHERE sc orehits.scoreid = leaguescorehitter.scoreid AND playersinleague.playerid = leagu escorehitter.playerid AND playersinleague.playerid = 1 AND playersinleague.id = 1 UNION ALL SELECT playersinleague.playerid,scorepitch.scorename,leaguescorepitc her.value FROM playersinleague,scorepitch,leaguescorepitcher WHERE playersinleag ue.playerid = leaguescorepitcher.playerid AND leaguescorepitcher.scoreid = score pitch.scoreid AND playersinleague.playerid = 1 AND playersinleague.id = 1; 1|0|2|SCAN TABLE leaguescorehitter (~1000000 rows) 1|1|0|SEARCH TABLE playersinleague USING COVERING INDEX sqlite_autoindex_players inleague_1 (id=? AND playerid=?) (~1 rows) 1|2|1|SEARCH TABLE scorehits USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 2|0|2|SCAN TABLE leaguescorepitcher (~1000000 rows) 2|1|0|SEARCH TABLE playersinleague USING COVERING INDEX sqlite_autoindex_players inleague_1 (id=? AND playerid=?) (~1 rows) 2|2|1|SEARCH TABLE scorepitch USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) What I don't understand is: All fields in WHERE clause are declared as primary/foreign keys. And it still gives full table scan on the first iteration. Can someone please help to solve it? Also is it checking conditions from left to right or right to left? Meaning the first row in the plan indicate equality against scorehits/scorepitch.scoreid, right? Thank you. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
