CODEWITHSUNDEEP
mysqlselect
quardas
quardas

Reputation: 691

mysql - how to get all differences from one table to another (huge amount of rows ~ 500k)

In the 

`synchro_newitems`

table I have all new items (from external source) ~ about 500k rows. It has one column:

`new_ids`

In the 

`synchro_olditems`

table I have all current items (also from external source) ~ about 500k rows. It has one column:

`old_ids`

I must get only the new items from synchro_newitems:

(NEW ITEMS) = synchro_newitems (-) synchro_olditems

I tried do that by insert the differences to the 3rd table:

INSERT INTO `synchro_diff` (`id`) 
SELECT DISTINCT new_ids FROM synchro_newitems 
LEFT JOIN 
synchro_olditems ON synchro_newitems.new_ids = synchro_olditems.old_ids 
WHERE synchro_olditems.old_ids IS NULL

(similarly with "NOT IN")

It's works for small amount of rows. But fails when there are 500 000 rows to compare.

I've tried simple:

DELETE FROM synchro_newitems WHERE exists(SELECT * FROM synchro_olditems)

But it dosent work.. Do you know some smart method to do that?

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

This is a weirdness of mysql queries. Try this:

select distinct new_ids
from synchro_newitems n
where not exists (select 1 from synchro_olditems o where n.new_ids = old.old_ids)

This optimizes better. And, better yet, put an index on o.old_ids to make it go blazing fast.

Upvotes: 1

Related Questions