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.
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
