Optimizing a left join

Question

I am trying to find some data from a table that's not in another table.

I tried to use different solutions but my problem is always the performance (table_a has ~100 000 rows and table_b has ~5.8 million rows).

Is there a fast(er) and/or (more) effective way to do that?

Thanks in advance...

SELECT
    *
FROM
    table_a a
LEFT JOIN
    table_b b ON b.field_one = a.field_one
WHERE 1
    AND b.id IS NULL

You could setup indices on the two tables, this would help the performance of the join. Have you done this? — Tim Biegeleisen, Jul 11 '16 at 14:39
Never use select *. In this case you are adding many unnecessary columns because you have no data in table b but you are returning the columns. That uses up resources. SELECT * is a SQL antipattern and it should not appear in production code. — HLGEM, Jul 11 '16 at 15:01

sagi · Accepted Answer · 2016-07-11T17:06:10.467

You can try using the NOT EXISTS() version of this, which may be a more efficient (not necessarily) :

SELECT * FROM Table_a a
WHERE NOT EXISTS(SELECT 1 FROM Table_b b
                 WHERE a.field_old = b.field_one)

You should also consider adding(if you don't have them yet) indexes on the tables :

Table_a(Field_one)
Table_b(Field_one)

Unless those tables are really wide(a lot of columns) , with this amount of data with the correct indexes it shouldn't take so long.

1 Answers1