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, Jan 31 '18 at 11:33

score 6 · Accepted Answer · answered Jan 31 '18 at 22:15

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!

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. — Kenneth, Feb 19 '23 at 21:32

score 0 · Answer 2 · answered Jan 31 '18 at 11:43

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 '18 at 11:43

A.D.

2,352
2
15
25

I have a matching index but only the left join profits from it – benjamin.d Jan 31 '18 at 12:21
So both queries provide you the same result or you facing any issue? – A.D. Jan 31 '18 at 12:23
They provide the same result set but with different plans and massive difference in execution times – benjamin.d Jan 31 '18 at 12:24
Time difference very because you have `n` number of operation with dependency. – A.D. Jan 31 '18 at 12:25
or you can check your query details by `explain` keyword followed by your query in mysql. – A.D. Jan 31 '18 at 12:27
I did. My original post spots the differences : full scan vs Index range – benjamin.d Jan 31 '18 at 12:28

score 0 · Answer 3 · answered Feb 11 '18 at 01:23

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 '18 at 01:23

Rick James

135,179
13
127
222

Mysql - LEFT JOIN way faster than INNER JOIN

3 Answers3