Mysql select where not in table

Question

I have 2 tables (A and B) with the same primary keys. I want to select all row that are in A and not in B. The following works:

select * from A where not exists (select * from B where A.pk=B.pk); 

however it seems quite bad (~2 sec on only 100k rows in A and 3-10k less in B)

Is there a better way to run this? Perhaps as a left join?

select * from A left join B on A.x=B.y where B.y is null; 

On my data this seems to run slightly faster (~10%) but what about in general?

Answer 1

I think your last statement is the best way. You can also try

SELECT A.*     from A left join B on      A.x = B.y     where B.y is null 

Answer 2

I use queries in the format of your second example. A join is usually more scalable than a correlated subquery.