Optimize query SQL (MySQL)

Question

I need to get all records from table1 minus all (matching) records in table2. Green zone in the image.

The matching has to be done by pairs (values from 2 columns) Here is the query that I'm doing:

SELECT col1, col2 FROM table1 WHERE (col1, col2) NOT IN (SELECT col1, col2 FROM table2)

tabel1 has 317789 records and table2 has 289639 records. I got index(col1), index(col2) and index(col1, col2) on both tables.

Query is runnig for over than 30min now... Help!! :)

IN/NOT IN with multiple values can't use indexes in MySQL. You should avoid it. — Vatev
– Vatev, Commented Apr 13, 2018 at 14:53
See case four of A Visual Explanation of SQL Joins = second Left outer join, and adjust for using pairs. — Henke
– Henke, Commented Apr 16, 2021 at 10:13

Tom Melly · Accepted Answer · 2018-04-13 14:51:27Z

4

I'd go with a null left join:

select col1, col2 from table1
left join table2 on table1.col1=table2.col1 and table1.col2 = table2.col2
where table2.<pk field> is null;

This will then show you the records from table1 that have no match in table2.

Note that pk field means whatever the primary key field in table2 is.

answered Apr 13, 2018 at 14:51

Tom Melly

3731 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Drew Over a year ago

Thanks, running it now.

Drew Over a year ago

Query still taking more than 35min... Is this normal for the volume of records that i have?? A simple Left/Right/Inner JOIN takes less than a minute over the same tables...

Tom Melly Over a year ago

It varies is all I can say. You could try something like: select * from table1 left join table2 on table2.key = (select key from table2 where table2.col1 = table1.col1 and table2.col2 = table1.col2) where table2.key is null;

godot · Accepted Answer · 2018-04-13 14:51:42Z

1

SELECT table1.col1, table1.col2 
FROM table1 LEFT JOIN table2 
ON table2.col1 = table1.col1 AND table2.col2 = table1.col2

look at this "cheatsheet" of joins

answered Apr 13, 2018 at 14:51

godot

3,5556 gold badges27 silver badges45 bronze badges

4 Comments

janith1024 Over a year ago

This is good but not solve Drews problem. Should be 'where table2.pk is null'

Drew Over a year ago

You forgot the "is null" clause. But thanks for the cheat sheet ;)

Tom Melly Over a year ago

The second one down on the left is the example you want. Also, iirc mysql does not support full outer joins, but there are ways to kludge one.

godot Over a year ago

you are right. I would not edit my answer and agree with first answer :)))

Collectives™ on Stack Overflow

Optimize query SQL (MySQL)

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related