On Wednesday, 5 November, 2014 05:14, vita...@yourcmc.ru said: >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:
>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. The two queries are different. They may end up with the same result, but you are asking different questions. In the first you are returning only matching rows. In the later you are requesting a projection (outer join) then applying the conditional (where) to the projection. An index on profiles.userid will speed it up, but you are still asking a different question. You cannot change the table visitation order around a left outer join because it changes the question. You ought to obtain the same result with SELECT * FROM bugs b CROSS JOIN profiles p WHERE p.userid=b.assigned_to AND p.login_name='vita...@yourcmc.ru' as well, it is the same inner join. However you have precluded the optimizer from re-ordering the tables in the join by using the cross join keyword, so the plan will be the same as the second (left outer join) rather than the first (equijoin) query. >Maybe you'll fix it in later sqlite versions? :) --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
