On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: > On 2014/11/05 14:13, 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: > > > > 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. > > These queries are very different, not equal in any way (semantically > or otherwise), the fact that they produce the exact same answer > is simply by virtue of your WHERE clause being specifically that and > your table data being being special. Drop the where clause and > they produce very different results for different table data.
I don't think it's anything to do with the table data being special, is it? Isn't it generically true that for any LEFT JOIN of a,b WHERE b.anything IS NOT NULL, the results are going to be equal with an INNER JOIN? > Why would you use left join for that? Maybe this is just a really > small extract from the real query you are doing and the real one > would shed more light? In some ways this looks very similar to the optimisation I was asking about a few weeks ago. The query is coming from the user, and is being "translated" into SQL from whatever the user puts into the actual UI. We normally don't make users type SQL into a text box :) In this specific query the filter criteria just *happen* to exclude unmatched results from the profiles tables and thus it turns out we *could* use an INNER JOIN and get the same results. You are right, we *could* make a query planner of our own, and squeeze it in between the simple UI->SQL translation and the SQL database. It could spot these optimisations and automatically change the type of join where it sees that it could. Or we could hope that the SQL database has a query planner of its own which can do such optimisations... :) -- dwmw2
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
