When you have an inner join like this:
SELECT *
FROM a JOIN b ON a.x=b.y
WHERE b.z=123;
then the SQL engine is free to interchange the order of the tables in
the join. For example, the join might be implemented as:
SELECT *
FROM b JOIN a ON a.x=b.y
WHERE b.z=123
Giving the SQL engine the freedom to interchange tables will often
create significant performance improvements. For example, if there
are indexes on b.z and a.x, then by interchanging the two tables, the
join can be satisfied by first looking up entries where b.z=123 then
finding corresponding b.y values and using them to look up entries
matching a.x=b.y.
Note that the tables can only be interchanged if you use a inner
JOIN. Change the order of tables in a LEFT JOIN creates a different
answer. So when you use LEFT JOIN, that forces a particular ordering
of tables, and greatly restricts the query engines opportunities to
optimize. So you should avoid using LEFT JOIN if you don't really
need it.
In the example above, because the tables can be reordered, the query
will run in O(logN). But if you us a LEFT JOIN forcing the original a-
before-b order, the runtime will be O(N*N). Quite a bit slower.
And in the example above, the LEFT JOIN is not really needed. Because
of the "b.z=123" test in the WHERE clause, the terms of the LEFT JOIN
where table b is NULL will never make it to the output. So you will
get the same result using either an inner JOIN or a LEFT JOIN. So
since an inner JOIN gives the query engine more optimization
opportunities, you might as well use it.
On Jan 10, 2009, at 8:58 AM, Lukas Haase wrote:
> D. Richard Hipp schrieb:
>> On Jan 9, 2009, at 3:16 PM, Lukas Haase wrote:
>>> SELECT t.topic, t.length
>>> FROM printgroup AS pg1
>>> LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup
>>> LEFT JOIN topics AS t ON t.topicID = pg2.topicID
>>> LEFT JOIN topic_ids AS ti ON ti.topicID = pg1.topicID
>>> WHERE ti.topic_textID = 'XXXX'
>>> ORDER BY pg2.topicID ASC;
>>
>> You seem very fond of using LEFT JOINs in places where they do not
>> make good sense.
>
> Yes, I started with mySQL 3 many years ago. At the beginning I only
> knew
> about LEFT JOINs and used them. Now I think I also know the other
> types
> of JOINs but I still use LEFT JOINs very often, just by habit. And
> with
> mySQL I never had performance problems with them.
>
>> What is it that you think a LEFT JOIN does?
>
> (A LEFT JOIN B) joins together table A and B while all records are
> taken
> from A and only records that match both are takes from B. If a record
> from A has no corresponding data in B, the values are NULL.
>
>> How is
>> a LEFT JOIN different than an ordinary inner JOIN?
>
> INNER JOIN takes *all* records from both tables, A and B. Generally,
> the
> resultset will be larger.
>
>> I ask because I
>> suspect that your answer will reveal misconceptions about LEFT JOINs
>> which, when rectified, will cause most of your performance issues to
>> go away.
>
> Maybe my I think too much in "left joining" but I did not know that
> there is so much difference in performance.
>
> Best Regards,
> Luke
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
