CODEWITHSUNDEEP
mysqlleft-joininner-joinperformance
Murvinlai
Murvinlai

Reputation: 2336

Left JOIN faster or Inner Join faster?

So... which one is faster (NULl value is not an issue), and are indexed.

SELECT * FROM A
  JOIN B b ON b.id = a.id
  JOIN C c ON c.id = b.id 
 WHERE A.id = '12345'

Using Left Joins:

SELECT * FROM A
 LEFT JOIN B ON B.id=A.bid
 LEFT JOIN C ON C.id=B.cid
WHERE A.id = '12345'

Here is the actual query Here it is.. both return the same result

Query (0.2693sec) :
    EXPLAIN EXTENDED SELECT * 
    FROM friend_events, zcms_users, user_events, 
    EVENTS WHERE friend_events.userid = '13006'
    AND friend_events.state =0
    AND UNIX_TIMESTAMP( friend_events.t ) >=1258923485
    AND friend_events.xid = user_events.id
    AND user_events.eid = events.eid
    AND events.active =1
    AND zcms_users.id = user_events.userid

EXPLAIN

    id select_type table type possible_keys key key_len ref rows Extra 
    1 SIMPLE zcms_users ALL PRIMARY NULL NULL NULL 43082 
    1 SIMPLE user_events ref PRIMARY,eid,userid userid 4 zcms_users.id 1 
    1 SIMPLE events eq_ref PRIMARY,active PRIMARY4 user_events.eid 1 Using where
    1 SIMPLE friend_events eq_ref PRIMARY PRIMARY 8 user_events.id,const 1 Using where



    LEFTJOIN QUERY: (0.0393 sec)

    EXPLAIN EXTENDED SELECT * 
    FROM `friend_events` 
    LEFT JOIN `user_events` ON user_events.id = friend_events.xid
    LEFT JOIN `events` ON user_events.eid = events.eid
    LEFT JOIN `zcms_users` ON user_events.userid = zcms_users.id
    WHERE (
    events.active =1
    )
    AND (
    friend_events.userid = '13006'
    )
    AND (
    friend_events.state =0
    )
    AND (
    UNIX_TIMESTAMP( friend_events.t ) >=1258923485
    )


EXPLAIN
    id select_type table type possible_keys key key_len ref rows Extra 
    1 SIMPLE friend_events ALL PRIMARY NULL NULL NULL 53113 Using where
    1 SIMPLE user_events eq_ref PRIMARY,eid PRIMARY 4 friend_events.xid 1 Using where
    1 SIMPLE zcms_users eq_ref PRIMARY PRIMARY 4 user_events.userid 1 
    1 SIMPLE events eq_ref PRIMARY,active PRIMARY 4 user_events.eid 1 Using where

Upvotes: 14

Views: 30380

Answers (4)

symfonian
symfonian

Reputation: 33

LEFT JOIN shows all data from A and only shows data from B/C only if the condition is true. As for INNER JOIN, it has to do some extra checking on both tables. So, I guess that explains why LEFT JOIN is faster.

Upvotes: 3

ChssPly76
ChssPly76

Reputation: 100706

It depends; run them both to find out; then run an 'explain select' for an explanation.

The actual performance difference may range from "virtually non-existent" to "pretty significant" depending on how many rows in A with id='12345' have no matching records in B and C.

Update (based on posted query plans)

When you use INNER JOIN it doesn't matter (results-wise, not performance-wise) which table to start with, so optimizer tries to pick the one it thinks would perform best. It seems you have indexes on all appropriate PK / FK columns and you either don't have an index on friend_events.userid or there are too many records with userid = '13006' and it's not being used; either way optimizer picks the table with less rows as "base" - in this case it's zcms_users.

When you use LEFT JOIN it does matter (results-wise) which table to start with; thus friend_events is picked. Now why it takes less time that way I'm not quite sure; I'm guessing friend_events.userid condition helps. If you were to add an index (is it really varchar, btw? not numeric?) on that, your INNER JOIN might behave differently (and become faster) as well.

Upvotes: 11

DJ.
DJ.

Reputation: 16247

The INNER JOIN has to do an extra check to remove any records from A that don't have matching records in B and C. Depending on the number of records initially returned from A it COULD have an impact.

Upvotes: 6

Mark Byers
Mark Byers

Reputation: 838216

Use EXPLAIN to see the query plan. It's probably the same plan for both cases, so I doubt it makes much difference, assuming there are no rows that don't match. But these are two different queries so it really doesn't make sense to compare them - you should just use the correct one.

Why not use the "INNER JOIN" keyword instead of "LEFT JOIN"?

Upvotes: 2

Related Questions