On Sep 5, 2013, at 8:56 PM, Yuzem <[email protected]> wrote: > SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies > GROUP BY genres ORDER BY genres; > time: 2.475s > > SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP > BY genres ORDER BY genres; > time: 0.035s
Yeah… the inner join vs out join will produce a significantly different access plan. For example, using a different schema: (1) inner join 0|0|1|SCAN TABLE movie_genre USING COVERING INDEX sqlite_autoindex_movie_genre_1 0|1|0|SEARCH TABLE genre USING INTEGER PRIMARY KEY (rowid=?) (2) outer join 0|0|0|SCAN TABLE genre USING COVERING INDEX sqlite_autoindex_genre_1 0|1|1|SCAN TABLE movie_genre USING COVERING INDEX sqlite_autoindex_movie_genre_1 In this case, the first plan is much better in terms of throughput as movie_genre is much larger table. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
