Left JOIN faster or Inner Join faster?

Question

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

Answer 1

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.