Igor Korot wrote: > EXPLAIN QUERY PLAN > SELECT positions.positionname, positionsforleague.value > FROM positionsforleague, positions, leagues > WHERE leagues.id = positionsforleague.id > AND positions.positionid = positionsforleague.positionid > AND leagues.name = "test"; > > 0|0|0|SCAN TABLE positionsforleague (~1000000 rows) > 0|1|1|SEARCH TABLE positions USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) > 0|2|2|SEARCH TABLE leagues USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
Are these rows estimates correct? Did you run ANALYZE? How many rows from each of the three tables end up in the actual result? > There is no WHERE filtering on positionsforleague table, so I'm not sure > what index to create. When joining with nested loops (which is the only method used by SQLite), the outermost table always uses SCAN TABLE because each record must be checked. The outermost table uses an index only if there is a filter on some column, but in this query, the only table with such a filter is leagues, and the query planner has decided to not use it as the outermost table anyway. You could force the join order by using CROSS JOIN like this (<http://www.sqlite.org/optoverview.html#manctrl>): SELECT ... FROM leagues CROSS JOIN positionsforleague CROSS JOIN positions WHERE ... AND leagues.name = "test" which will use an index on name, if available, but this is not necessarily faster; you have to measure it. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
