Can this be optimized/done better?
Bit of background:
I have a product database, and now an external product database with extra information/etc... I need to match THEIR product database to MY product database.
The external database has roughly 1,200,000 while my internal one contains 500,000.
The way I do it now, is do it in batches of 20,000 from 0 to the highest product # which is 1 billion. I'm thinking this may not be the best way because there are many batches which will not contain 20,000 records.
But in this example, $low and $high would be 20,000 apart.
SELECT external.productid, external.mfgpartno, mfr_xref.internal_mfr_id
FROM external_product AS external
INNER JOIN mfr_xref ON mfr_xref.external_mfr_id = external.manufacturerid
LEFT JOIN prod_xref ON prod_xref.external_product_id = external.productid
external.productid BETWEEN $low AND $high
AND prod_xref.external_product_id IS NULL
Any ideas for optimizing maybe not only the query, but the process too?