Why LEFT JOIN is terribly slower than INNER JOIN?

Question

I have two tables, toynav_product_import - 18533 rows, catalog_product_entity - 42000 rows.

The below query, LEFT JOIN takes more than 2 minutes, while INNER JOIN runs in 0.009 seconds. The first table has the necessary index for the barcode field.

SELECT tpi.barcode FROM  toynav_product_import tpi
INNER  JOIN catalog_product_entity cpe ON tpi.barcode = cpe.sku

Please advise

toynav_product_import

catalog_product_entity

The question is explain here: https://stackoverflow.com/questions/27787269/why-is-left-join-slower-than-inner-join/27788084 — Ismoil Shifoev, May 23 '19 at 07:15

score 1 · Answer 1 · answered May 23 '19 at 06:56

An outer join ( LEFT JOIN or RIGHT JOIN ) has to do all the work of an INNER JOIN plus the extra work of null-extending the results

And even if a LEFT JOIN were faster in specific situations, it is not functionally equivalent to an INNER JOIN, so you cannot simply go replacing all instances of one with the other!

Sorry cant post this as a comment

score 1 · Answer 2 · edited May 23 '19 at 07:21

1

A LEFT JOIN is slower than the Inner Join. By definition, an outer join (LEFT JOIN or RIGHT JOIN) has to do all the work of an INNER JOIN plus the extra work of null-extending the results, Thats the reason. And as it also returns more number of Rows as compare to inner join, Thats why execution takes more time. But by indexing the Foreign Keys properly, you can definitely increase the performance of the Joins.

It also depends on the Data, Its not always the case that Left join is slower, There are the cases when Left join is faster, But mostly Inner join is faster according to above described reasons. Please refer to this link, the guy explained the difference very clearly.

edited May 23 '19 at 07:21

Ismoil Shifoev

5,512
3
24
32

answered May 23 '19 at 07:14

Asad Ali Choudhry

4,985
4
31
36

I agree with that. But in this case, both should produce the same number of results. There should not be a 2 minutes difference at all between both queries. – Duke May 23 '19 at 07:40
Even if the number of rows are same in the result. Still system do the same stuff to check extra number of rows. But here why you don't make SKU as Foreign Key? Adding Index and Adding Foreign key will definitely improve the performance. – Asad Ali Choudhry May 23 '19 at 07:46

Why LEFT JOIN is terribly slower than INNER JOIN?

2 Answers2