Why is OUTER JOIN faster than INNER JOIN?

Question

MySQL

I synchronize prices in the database, I use a temporary table for this

CREATE TEMPORARY TABLE `tmp_products` (
  `main_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `sale_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `product_id` int(11) DEFAULT NULL,
  `option_value_id` int(11) DEFAULT NULL,
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When updating product options, I execute the following request

UPDATE `oc_product_option_value` `pov`
INNER JOIN `tmp_products` `tmp` 
    ON `tmp`.`product_id` =  `pov`.`product_id` 
    AND `tmp`.`option_value_id` =  `pov`.`option_value_id`
SET  `pov`.`price` = `tmp`.`price`;

But the problem is that the execution time reaches 5 minutes (there are only about 1700 options), and if you replace the connection with a LEFT JOIN request, it will be completed in 1 minute. I solved the problem by adding an index INDEX (`product_id`, `option_value_id`), I just would like to understand the reason for such a difference, while the inner join should work faster than the outer one. Thanks in advance!

Answer 1

because the execution plans are different. compare plans, there is an answer