Is this the optimum query for what I'm trying to do in MySQL?

Question

I'm running a number of queries that merge constantly-changing data into a master table and one of the queries (below) seems to be running quite slowly.

The set up is as follows: products table and products_temp table have identical structures. New data goes into the products_temp table, then I run queries similar to the one below to merge the new data with the master products table.

INSERT INTO products ( name, brand, price, feeds_id, img_url, referral_url, productid, isbn, ean, upc )
SELECT name, brand, price, feeds_id, img_url, referral_url, productid, isbn, ean, upc
FROM products_temp 
WHERE feeds_id = 449
AND productid NOT IN (
    SELECT productid
    FROM products
    WHERE feeds_id = 449
)

Both of these tables have indexes on the feeds_id but I have a feeling that isn't making any difference.

As an example products may contain over 3.5 million rows and products_temp may contain 50,000 to merge products.

So my question really is how long should that take? How quick can I make it?

Answer 1

It is, this technique is called Shadow Table trick.

Answer 2

you should greatly avoid WHERE x not in (select xxx). The mysql query optimizer is very bugged with subqueries and will ignore indexes for example.