Professional Web Applications Themes

Is left-join faster then inner join? - MySQL

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

Sponsored Links
  1. #1

    Default Is left-join faster then inner join?

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

    Thanks...

    Sponsored Links
    howachen@gmail.com Guest

  2. #2

    Default Re: Is left-join faster then inner join?

    [email]howachen[/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]
    Axel Schwenke Guest

  3. #3

    Default Re: Is left-join faster then inner join?


    Axel Schwenke 寫道:
    > [email]howachen[/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?

    howachen@gmail.com Guest

  4. #4

    Default Re: Is left-join faster then inner join?


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

    Axel Schwenke ??:
    > [email]howachen[/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)



    Luuk Guest

  5. #5

    Default Re: Is left-join faster then inner join?

    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[/email] wrote:
    > Some people said that using left-join is generally faster than inner
    > join, is that true?
    >
    > Thanks...
    >
    Dikkie Dik Guest

  6. #6

    Default Re: Is left-join faster then inner join?

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

Similar Threads

  1. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  2. left join limit 1
    By in forum MySQL
    Replies: 7
    Last Post: March 27th, 04:38 AM
  3. Left join isn't joining
    By Bill in forum MySQL
    Replies: 7
    Last Post: December 23rd, 04:47 PM
  4. SQL query with a Left outer Join
    By Ad Bec in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 22nd, 04:48 AM
  5. left join problem
    By AK in forum IBM DB2
    Replies: 8
    Last Post: August 8th, 12:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139