0

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

enter image description here

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

enter image description here

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?

UPDATE

Including explain for Akina's suggested query

enter image description here

3
  • Edit the question and add the output of SHOW CREATE TABLE for both tables. – ypercubeᵀᴹ Jan 15 '19 at 8:24
  • The problem has already been solved, I will edit the question to include the answer – Chechy Levas Jan 15 '19 at 8:28
  • 1
    If you have a solution, please provide it in an answer, not in the question. And it would still be good to edit the question with the information I asked. Performance depends on many things like indexes available, column types, table engines. We don't know if the tables are using InnoDB, MyISAM, Memory engine or somethign else. We don't know if they are temporary or not. We don't know their sizes. All that would be useful in order to provide an answer. – ypercubeᵀᴹ Jan 15 '19 at 8:31
0

Try

SELECT t.*
FROM temp_main_working t
WHERE NOT EXISTS ( SELECT 1
                   FROM temp_sf s 
                   WHERE t.pm_order_id = s.pm_order_id )

UPDATE

id select_type        table partitions type   possible_keys key     key_len ref                rows filtered Extra
1  PRIMARY            t     null       index  null          PRIMARY 4       null               1    100      Using where; Using index
2  DEPENDENT SUBQUERY s     null       eq_ref PRIMARY       PRIMARY 4       test.t.pm_order_id 1    100      Using index
6
  • I get pretty much the same explain. The only change is 'select_type' is no 'DEPENDENT SUBQUERY'. – Chechy Levas Jan 15 '19 at 7:57
  • @ChechyLevas I get pretty much the same explain. 1) The execution plan is dependent by data statistics. And it can change if the statistics change. 2) Efficiency is related to execution time and/or resource usage, not execution plan beauty. – Akina Jan 15 '19 at 8:02
  • My original query runs for over 5 minutes without returning and I let yours run fir 40 seconds before killing. This question is as much about the theoretical background/general solution as it is about the practical solution to this specific problem, so your comment on statistics is well received (I did not know this). In any case, both queries take longer than I think they should, and the execution plan seems to be the best clue as to why. – Chechy Levas Jan 15 '19 at 8:08
  • @ChechyLevas EXPLAIN added. Look Extra. – Akina Jan 15 '19 at 8:12
  • 1
    @ChechyLevas The datatypes on the join columns are not the same. Correct this problem. – Akina Jan 15 '19 at 8:27
0

The problem was because the datatypes on the join columns are not the same. Changing the types to match solves the problem completely.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.