CODEWITHSUNDEEP
mysqlsqlperformanceleft-joinnotin
Denes Csesznegi
Denes Csesznegi

Reputation: 25

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

Upvotes: 0

Views: 59

Answers (1)

sagi
sagi

Reputation: 40481

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.

Upvotes: 1

Related Questions