LEFT JOIN much faster than INNER JOIN

Question

I have two tables ->

• users

  user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
  username VARCHAR(200) NOT NULL
  

• pings

  ping_id INT UNSIGNED NOT NULL AUTO_INCREMENT PPRIMARY KEY
  disqus_id VARCHAR(32) NOT NULL
  user_id INT UNSIGNED NOT NULL
  
  INDEX combo1(disqus_id,user_id)
  

I run this query ->

EXPLAIN SELECT pings.*, username
          FROM pings
    INNER JOIN users USING(user_id)
         WHERE pings.disqus_id = 'post_168' AND user_id = '1'
      ORDER BY pings.ping_id DESC LIMIT 2

The Extracolumn under pimgs says Using where; Using temporary; Using filesort and under users says Using where; Using join buffer.

I run this query ->

EXPLAIN SELECT pings.*, username
          FROM pings
     LEFT JOIN users USING(user_id)
         WHERE pings.disqus_id = 'post_168' AND user_id = '1'
      ORDER BY pings.ping_id DESC LIMIT 2

The Extracolumn under pimgs says Using where and under users it is empty.

What is happening?

Answer 1

You're creating 2 different queries that have 2 different sets of requirements for producing, what may happen to be, the same result set:

Query 1: (INNER JOIN): You're stating that it can only return rows from table ping where there is a matching user_id value in the table users. That's why there's extra usings for checking.

Query 2: (LEFT JOIN): You're stating that it can return all rows from table ping and any rows that match user_id from table users.

The distinguishing issue here is whether or not you're requiring that there be a matching value in the second table. In Query 1 it has to add extra checking to ensure that there's a matching value, in Query 2, it doesn't.

Answer 2

• The USING(column_list) clause names a list of columns that must exist in both tables.

so if you are using user_id must be in both tables , but left join will return the values in table a and not in table b

The following two clauses are semantically identical:

a LEFT JOIN b USING (c1,c2,c3)
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3