cody <[EMAIL PROTECTED]> writes: > > Why is this (1,44 seconds) > > select * from movies m > where m.movieid in (select mp.movieid from people p, moviepeople mp > where p.fullname="Spencer, Bud" and mp.personid=p.personid) > > much faster than the following (Runtime several minutes!): > > select * from movies m > join moviepeople mp on mp.movieid=m.movieid > join people p on p.personid=mp.personid > where p.fullname="Spencer, Bud" > > Shouldn't it perform equally? > ... > The database was currently vacuumed and analyzed. >
Have you tried asking it to EXPLAIN ? It may be that the JOIN does the join on the whole tables first, then applying the WHERE, whereas the subselect is working on a much reduced subset of data. Google for "subselect faster than join" - some interesting comments ! (e.g. suggesting that EXISTS might be faster again than IN) Regards, MikeW _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
