I am using MySQL 5.7 (can't change this), and I am kind of new to MySQL. I have a fair amount of experience with SQL sever so just trying to work through some of these quirks.
The query I want to run is
select * from temp_main_working t left outer join temp_sf s on t.PM_ORDER_ID = s.pm_order_id where s.pm_order_id is null
The explain looks like this
If I change the outer join to an inner join (which obviously won't give me what I want), as such
select * from temp_main_working t inner join temp_sf s on t.PM_ORDER_ID = s.pm_order_id
I get an explain that looks like this
which is obviously much better for performance.
In other cases where i use a left join, it managed to use associated indexes just fine. What is going on here? How do I get it to run efficiently?
Including explain for Akina's suggested query