Denes Csesznegi - 9 months ago 55

MySQL 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

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.