MySQL Performance: update vs outer join

Question

I am a bit puzzled: I understand that the general experience is that outer joins are fast. Well, my case here is different: Need to add one field from the sds table to the drug table, based on one common key field:

select drug.*, sds.name 
from drug
left outer join sds 
on drug.id = sds.id;

Table sizes: drug 25,000 rows sds 65,000 rows matching 24,000 rows on field "id" Both tables are indexed on the id field

The first query above did not return a result after 2 minutes, then I aborted.

So I redid the query with a temporary table:

create temporary table tmp select *, space( 50) as name from drug;
create index id on tmp ( id);
update tmp, sds 
    set tmp.name = sds.name 
    where tmp.id = sds.id;

All this together took less than one second cpu time.

Does this make sense? she we avoid outer joins like the plague?

Please provide SHOW CREATE TABLE for both tables. It is unusual to JOIN on `id` since `id` is usually a separate `PRIMARY KEY` for each table. — Rick James, Feb 25 '15 at 22:10

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

Here is a good explanation of INNER vs OUTER joins. You will see each has it place but they have trade offs too.

What is the difference between "INNER JOIN" and "OUTER JOIN"?

After reading your question again... I really think you should be looking at an INNER JOIN as you stated 24,000 matching rows.

SELECT 
  d.*, 
  s.name 
FROM 
  drug d INNER JOIN sds s on d.id = s.id;

This should only return the 24,000 rows that match and be much faster because you are excluding rows from both tables on query.

edited May 23 '17 at 12:10

Community

1
1

answered Feb 25 '15 at 14:02

Lars

615
1
4
13

if you use aliases `d` and `s` you need to call fields `d.*` `s.name`? Don't we? – Alex Feb 25 '15 at 14:18
That link is good for standard SQL. For MySQL, `INNER` and `OUTER` and `CROSS` are ignored; `LEFT`, `RIGHT`, `ON` and `WHERE` determine whether it is INNER or OUTER or CROSS. `FULL` does not exist, though you can achieve it with a UNION. – Rick James Feb 25 '15 at 22:16

score 0 · Answer 2 · answered Feb 25 '15 at 14:02

0

Try to check (I don't see any reason to use outer):

select drug.*, sds.name 
from drug
left join sds 
on drug.id = sds.id;

answered Feb 25 '15 at 14:02

Alex

16,739
1
28
51

Alex is correct, OUTER joins have a very specific usage and can cause issue when used incorrectly. Chances are a LEFT join will yield the result you are looking for and will be faster. – Lars Feb 25 '15 at 14:08
In MySQL, the keyword `OUTER` is ignored. – Rick James Feb 25 '15 at 22:16
give me a link to where it is written – Alex Feb 25 '15 at 23:50
I understand that `LEFT JOIN` and `LEFT OUTER JOIN` are synonymous; so, no change here. I did rerun the query with `LEFT JOIN` tough and still did not get a result within 2 minutes. – Wolf Metzner Feb 27 '15 at 21:26

score 0 · Answer 3 · edited May 23 '17 at 10:24

The sql does not specify if a left or right outer join is to be used, thus the default is for a full outer join. The sql will then return the union of drug and sds, i.e. all the rows in drug and all the rows in sds.

In terms of a Venn diagram, the inner joins generally will give you the parts of the diagram that are intersecting.

A very detailed look at joins: What is the difference between "INNER JOIN" and "OUTER JOIN"?

MySQL Performance: update vs outer join

3 Answers3