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.
To clarify - Producing the same results does not imply semantic equality, if I asked you to find me some people of a specific kind
in a specific bus, and someone else asks you to find the same type of people in the New-York downtown traffic... it "may" end up
that those just happen to be the exact same people at the end, but the methods which you employ to look for those people will be
significantly different, the result coinciding does not make the syntax semantically equal.
Telling SQL (of any flavour) to "Left join" is asking for a very different method of computation than when asking it to "inner join"
and it should use the best tools for the request, it cannot see into the future to know the result will be fine if it stealthily
just does the other method. (Though Analyze might help it to see a bit clearer and take that decision differently).
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?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
