CODEWITHSUNDEEP
mysqlperformanceleft-joininner-joinmariadb
benjamin.d
benjamin.d

Reputation: 2871

Mysql - LEFT JOIN way faster than INNER JOIN

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:

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 ?

Upvotes: 4

Views: 5738

Answers (3)

Rick James
Rick James

Reputation: 142306

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.

Upvotes: 0

Tim Mickey
Tim Mickey

Reputation: 361

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!

Upvotes: 6

A.D.
A.D.

Reputation: 2372

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.

Upvotes: 0

Related Questions