Can this be optimized/done better?

[triexa]

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
WHERE
	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?

[r937]

i'm not sure i understand the question

"I'm thinking this may not be the best way"

the best way for what? what are you trying to do?

[triexa]

I need to match THEIR products to MY products... that simply fetches out the ones that don't match, then I will see if I have a match based on the mfr part # (separate).

Uses the cross reference table to select the rows that are NOT matched...

[r937]

cross reference table??? not matched????

sorry, even less clear now

[triexa]

there is a cross reference table containing THEIR product id and OUR product id.

Grabbing the ones from THEIR product table that are not matched (THEIR product id does not exist in the cross reference table)

[IBazz]

uh? so you have three tables...

your_products
| your_prodict_id | your stuff

their_products
| their_product_id | their stuff |

cross_reference
| your_product_id | their product_id |

I don't know how to write the query but I think you should want to select those products in their table, which do not match their product_id in your cross_reference table.

pseudo code..

select their_product_id
from their_table
WHERE their_product_id NOT IN 
( SELECT their_product_id 
  FROM cross_reference
)

hth

bazz

[triexa]

bazz, that logic is basically what im doing... if they aren't in the cross reference, they do not match... so I am left joining and only including where the match is NULL (no match)...

[Intaglio5]

the NOT IN () is not as efficient as it could be. You could do an inner join ON id = NULL, that's the same as doing the NOT IN and I think it's faster

SELECT
t1.id
FROM
t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id AND t2.id = NULL)

this should return all of the records that don't match

[r937]

ON (t1.id = t2.id AND t2.id = NULL)

no, sorry, that won't work

if t2.ID is NULL, then it won't be equal to t1.id, ever

that query will return 0 rows

[Intaglio5]

erm, you're right, I meant to write LEFT OUTER JOIN, not INNER JOIN. Brain sneeze.