Professional Web Applications Themes

Is Left Join faster than Inner Join? - MySQL

Removed by Administrator...

  1. Moderated Post

    Default Is Left Join faster than Inner Join?

    Removed by Administrator
    howa Guest
    Moderated Post

  2. Moderated Post

    Default Re: Is Left Join faster than Inner Join?

    Removed by Administrator
    Good Guest
    Moderated Post

  3. Moderated Post

    Default Re: Is Left Join faster than Inner Join?

    Removed by Administrator
    strawberry Guest
    Moderated Post

  4. #4

    Default Re: Is Left Join faster than Inner Join?

    On Jun 15, 11:50 pm, strawberry <com> wrote: 

    Can't get your meaning?

    howa Guest

  5. #5

    Default Re: Is Left Join faster than Inner Join?

    On Jun 15, 5:10 pm, howa <com> wrote: 
    >
    > Can't get your meaning?[/ref]

    Taking the example of the schema I posted yesterday in another thread,
    the following queries will both produce the same result - but the LEFT
    JOIN takes twice as long (although I'm not sure of the significance of
    that ratio):

    SELECT * FROM food_category fc
    LEFT JOIN food f ON f.id = fc.food_id;
    (0.034 secs)

    SELECT * FROM food_category fc
    JOIN food f ON f.id = fc.food_id;
    (0.017 secs)

    LEFT JOINS and RIGHT JOINS are syntactically identical, therefore we
    should expect them to take exactly the same time:

    SELECT *
    FROM food f
    RIGHT JOIN food_category fc ON f.id = fc.food_id
    (0.034 secs)

    However, the question is misleading because it's rare that one type of
    join can, with certainty, be used in place of another. In this
    example, it's merely accidental that both queries produce the same
    result set.

    strawberry Guest

  6. #6

    Default Re: Is Left Join faster than Inner Join?

    On 6 16 , 12 34 , strawberry <com> wrote:
     

    usually, inner join can be replace by left join, provided that we
    don't allow null value on some fields

    now, considering the inner and left join,

    since left join will always return the rows in the left table, so i
    believe...

    less cross checking => generally faster


    howa Guest

  7. #7

    Default Re: Is Left Join faster than Inner Join?

    howa <com> wrote: 
    >
    > usually, inner join can be replace by left join, provided that we
    > don't allow null value on some fields[/ref]

    Which is - of course - complete nonsense.

    First you ask the database to add filler rows (those with NULLs)
    and then you ask it to remove them (in WHERE). Do you really think,
    the database gets faster if you put some extra work on it?
     

    Nope. Also LEFT JOIN has to check if a matching row exists in the
    other table. And insert NULLs otherwise. So if the JOIN order is
    the same, then LEFT JOIN does the same number of lookups in the
    "right" table than an INNER JOIN.

    But once more: INNER and OUTER JOIN in general produce different
    results. So there is absolutely *no* point in asking which one
    is faster. The choice is based on the expected result anyway.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Replies: 2
    Last Post: September 18th, 09:59 PM
  3. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  4. Select Left Join AND Right Join
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 03:42 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