>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. >
Foreign Key declarations specify referential integrity constraints. Primary Key declarations necessarily create an index to enforce uniqueness (and so that the value can be used to find the row referenced by the key). Similarly a UNIQUE constraint also creates an index to enforce uniqueness. Query plan optimization is based on choosing the lowest cost nested loop order because *appropriate indexes exist* which can be used to find the data of interest more quickly than a sequential scan of the entire table looking for rows which satisfy the selection constraints (WHERE clauses) -- or which create visitation order that might partially satisfy an ORDER BY or GROUP BY ... Have you declared useful indexes so that the data you are looking for can be found without having to resort to a table scan? You need to manually create indexes on *both* the source and target of a foreign key if they are not otherwise created by a constraint which does create an index, such as "primary key" or "unique" constraints -- they are not created for you. If you have created appropriate indexes, have you run the ANALYZE command to gather index distribution statistics for the query optimizer? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
