On Wed, Nov 5, 2014 at 7:13 AM, <vita...@yourcmc.ru> wrote: > Hi! > > After playing a little with SQLite as a DBMS for Bugzilla, I've discovered > that LEFT/INNER join affects query plan in a bad way even for semantically > equal queries: >
I'm not sure what you mean by "semantically equal", but INNER JOIN and LEFT JOIN do compute different answers in many cases. And in particular, the first query plan below (the one for INNER JOIN) will compute the wrong answer for a LEFT JOIN. SQLite seems to be doing the right thing here. > > SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE > p.login_name='vita...@yourcmc.ru' > > Query plan: > SEARCH TABLE profiles AS p USING INDEX profiles_login_name_idx > (login_name=?) > SEARCH TABLE bugs AS b USING INDEX bugs_assigned_to_idx (assigned_to=?) > > But > > SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE > p.login_name='vita...@yourcmc.ru' > > Query plan: > SCAN TABLE bugs AS b > SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?) > > Which is of course very slow. > > Maybe you'll fix it in later sqlite versions? :) > > -- > With best regards, > Vitaliy Filippov > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
