Denes Csesznegi Denes Csesznegi - 4 months ago 17
MySQL Question

Optimizing a left join

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

Answer

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.

Comments