Is left-join faster then inner join?

[howachen@gmail.com]

Some people said that using left-join is generally faster than inner
join, is that true?

Thanks...

[Axel Schwenke]

[email]howachen@gmail.com[/email] wrote:

> Some people said that using left-join is generally faster than inner
> join, is that true?

Again: some people may be stupid.

LEFT JOIN and INNER JOIN are different things. You cannot compare them.

If you write an INNER JOIN as LEFT JOIN you will either end up with
different results or - if you filter the NULL rows of the LEFT JOIN -
there is good chance for the LEFT JOIN to be slower. Because:

- the LEFT JOIN creates extra intermediate data (the NULL rows for
unmatched rows from the left table) that is thrown away later

- the optimizer has less freedom to optimize the LEFT JOIN


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]

[howachen@gmail.com]

Axel Schwenke 寫�：

> [email]howachen@gmail.com[/email] wrote:

> > Some people said that using left-join is generally faster than inner
> > join, is that true?

>
> Again: some people may be stupid.
>
> LEFT JOIN and INNER JOIN are different things. You cannot compare them.
>
> If you write an INNER JOIN as LEFT JOIN you will either end up with
> different results or - if you filter the NULL rows of the LEFT JOIN -
> there is good chance for the LEFT JOIN to be slower. Because:
>
> - the LEFT JOIN creates extra intermediate data (the NULL rows for
> unmatched rows from the left table) that is thrown away later
>
> - the optimizer has less freedom to optimize the LEFT JOIN
>
>
> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB
>
> Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
> MySQL User Forums: [url]http://forums.mysql.com/[/url]

If the "NULL values problem" can be ignored, is that left-Join faster
than inner join?

[Luuk]

<howachen@gmail.com> schreef in bericht
news:1149948666.324312.166230@h76g2000cwa.googlegr oups.com...

Axel Schwenke ??:

> [email]howachen@gmail.com[/email] wrote:

> > Some people said that using left-join is generally faster than inner
> > join, is that true?

>
> Again: some people may be stupid.
>
> LEFT JOIN and INNER JOIN are different things. You cannot compare them.
>
> If you write an INNER JOIN as LEFT JOIN you will either end up with
> different results or - if you filter the NULL rows of the LEFT JOIN -
> there is good chance for the LEFT JOIN to be slower. Because:
>
> - the LEFT JOIN creates extra intermediate data (the NULL rows for
> unmatched rows from the left table) that is thrown away later
>
> - the optimizer has less freedom to optimize the LEFT JOIN
>
>
> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB
>
> Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
> MySQL User Forums: [url]http://forums.mysql.com/[/url]

If the "NULL values problem" can be ignored, is that left-Join faster
than inner join?


When there is 180 km/h on the speedometer of my car, can i do 180 km/h?
NO,

Maybe you get the same results, but bevause of differen functionallity you
should NOT do this! (like Axel said)

[Dikkie Dik]

I think others in this thread have given enough answer already.

A former colleague of me told that there was a time when MySQL supported
left joins but not inner joins. Anyway, that was his explanation for
the enormous quantities of left joins in the code that should really
have been inner joins.

There are a few things to say about this.
First, the only way to be sure is to test it.
Second, if the query is not a performance bottleneck anyway, it may a
good to optimize it for legibility. If code is not absolutely clear, it
is a maintenance problem that needs to be fixed.
Third, as an object-oriented programmer, I find lazy collections faster
than joins. Especially because the collections can optimize their
database "strategy": Some of them use SELECT statements, others use
HANDLER statements. Some are greedy (load all in one go), some are lazy
(load something only when needed), and some can be scheduled, so that
anything not needed this microsecond can be read in the next batch.
Anyhow, no data is read more than once in a page building session. Off
course, these optimizations all reside inside the table wrapper classes,
and have little to no effect on the surrounding code.

Best regards

[email]howachen@gmail.com[/email] wrote:

> Some people said that using left-join is generally faster than inner
> join, is that true?
>
> Thanks...
>

[Saeed]

with my experience I say yes. I have a product table with 12 reference fields and 1000 records. it took about 16 secs to fetch data from that table but when I changed all jions to left join it's duration changed to 0.125 sec! and also I need the null fields so the result was OK for my problem. it was same for right join but result wasn't the one I expected.