Mysql - LEFT JOIN way faster than INNER JOIN

Question

I have two tables

Table X: millions or records

|-----|-----|-----|-----|
|  a  |  b  |  c  |  d  |
|-----|-----|-----|-----|

Table Y: only a few records

|-----|-----|
|  e  |  f  |
|-----|-----|

X.d allows me to join both tables on X.d = Y.e

I have the following indices:

(X.a)
(X.b)
(X.c)
(X.d)
(X.a, X.b, X.c, X.d)
(Y.e)

One of our application was executing the following query, which took ages to run:

SELECT * 
FROM X
INNER JOIN Y ON X.d = Y.e
WHERE 
      X.a in (1, 2, 3)
  AND X.b IS NULL
  AND X.c in (4, 5 ,6)
  AND X.d in (7, 8, 9)

After changing the INNER JOIN to a LEFT JOIN, the query was extremely fast:

SELECT * 
FROM X
LEFT JOIN Y ON X.d = Y.e
WHERE 
      X.a in (1, 2, 3)
      AND X.b IS NULL
      AND X.c in (4, 5 ,6)
      AND X.d in (7, 8, 9)

Looking at explain plans for these queries, first query is doing a full scan when the second is only doing an Index Scan (range) on my compound index. I saw other posts on SO but they had different scenarios.

Why such a diffence in the plans ?

I don't know the reason, but you can try "fixing" it with USE KEY or FORCE KEY. — Vatev
– Vatev, Commented Jan 31, 2018 at 11:33

Tim Mickey · Accepted Answer · 2018-01-31 22:15:56Z

6

The reason for the different plans is that LEFT JOIN will force the join order of your tables to match the order they appear in your query. Without the left join, the optimizer will choose the join order for you, and in this case it will choose the very small table first. (You can see this in your explain by looking at the order the tables are listed.) Once your join order is switched, the index for X changes to KEY d which must have a much larger data set than the compound key.

To fix this, change your select to SELECT STRAIGHT_JOIN *. This is preferred over USE INDEX so that the optimizer can still choose the best key for table X... You might find a better compound key than a,b,c,d, or if your data in X changes dramatically, one of your other keys may be better after a point.

I have to point out, that you normally can't just switch to a LEFT JOIN. The data returned will usually be different!

answered Jan 31, 2018 at 22:15

Tim Mickey

3613 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

benjamin.d Over a year ago

Thanks. In my case the result set is the same

Kenneth Over a year ago

STRAIGHT_JOIN absolutely blew my mind. You hit the nail on the head, when I EXPLAIN both queries, the join order was backwards resulting in a much much slower query in my case. With the STRAIGHT_JOIN, it followed same order as I had them in the query.

A.D. · Accepted Answer · 2018-01-31 11:43:05Z

0

A LEFT JOIN is not faster than INNER JOIN. It always depends on the structure of your table whereas the proper key index is applied to that or not. If there you do not use a Dependency Or Index Undoubtedly the Left Join is way faster because that not Scan Complete table. But if the scenario change and both tables depend on a proper cluster index then both the Join provide data as feasible as they can.

Left Join is always faster if you not use a proper indexing any of your tables. Also sometimes it depends on data and data structure because every scenario has their own sufficient Logics.

Post INNER JOIN vs LEFT JOIN For Example this having relative to MsSQL but applied to both MySql and MsSql.

answered Jan 31, 2018 at 11:43

A.D.

2,3722 gold badges17 silver badges25 bronze badges

6 Comments

benjamin.d Over a year ago

I have a matching index but only the left join profits from it

A.D. Over a year ago

So both queries provide you the same result or you facing any issue?

benjamin.d Over a year ago

They provide the same result set but with different plans and massive difference in execution times

A.D. Over a year ago

Time difference very because you have n number of operation with dependency.

A.D. Over a year ago

or you can check your query details by explain keyword followed by your query in mysql.

|

Rick James · Accepted Answer · 2018-02-11 01:23:42Z

0

Drop INDEX(a) as being redundant with your composite index

Replace INDEX(b) with INDEX(b,d).

Then provide EXPLAIN SELECT ... so we can discuss things further.

answered Feb 11, 2018 at 1:23

Rick James

144k15 gold badges144 silver badges254 bronze badges

Collectives™ on Stack Overflow

Mysql - LEFT JOIN way faster than INNER JOIN

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related