mysql - how to get all differences from one table to another (huge amount of rows ~ 500k) <p>In the </p> <pre><code>`synchro_newitems` </code></pre> <p>table I have all new items (from external source) ~ about 500k rows. It has one column:</p> <pre><code>`new_ids` </code></pre> <p>In the </p> <pre><code>`synchro_olditems` </code></pre> <p>table I have all current items (also from external source) ~ about 500k rows. It has one column:</p> <pre><code>`old_ids` </code></pre> <p>I must get only the new items from <code>synchro_newitems</code>:</p> <p>(NEW ITEMS) = <code>synchro_newitems</code> (-) <code>synchro_olditems</code></p> <p>I tried do that by insert the differences to the 3rd table:</p> <pre><code>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 </code></pre> <p>(similarly with "NOT IN")</p> <p>It's works for small amount of rows. But fails when there are 500 000 rows to compare.</p> <p>I've tried simple:</p> <pre><code>DELETE FROM synchro_newitems WHERE exists(SELECT * FROM synchro_olditems) </code></pre> <p>But it dosent work.. Do you know some smart method to do that?</p>