MySql: Why Is this Query running so slow?

Question

OK So I have this list of products that are in the cart. I am trying to retrieve all the related items to the items in the cart without pulling any items that are in the cart.

I wrote this query and it pulls exactly what I want, but its taking like 8 seconds to run.

SELECT * FROM cart_product 
WHERE product_id 
IN(
    SELECT product_related_related_id FROM `cart_product_related` 
    WHERE product_related_product_id 
    IN (5401,5402,4983,5004)
) 
AND product_id 
NOT IN(5401,5402,4983,5004)

Showing rows 0 - 2 (3 total, Query took 7.9240 sec)

Is there some way I can optimize this to make it run faster?

Thanks!

Answer 1

You should replace the subquery with a JOIN, it should speed things up.

Something like this:

SELECT DISTINCT cart_product.* FROM cart_product 
  INNER JOIN cart_product_related ON product_id = product_related_related_id
WHERE product_related_product_id IN (5401,5402,4983,5004)
  AND product_id NOT IN(5401,5402,4983,5004)

Answer 2

Try adding an index on product_related_product_id and product_id if you don't already have them. Unless you have a massive amount of data, I can only guess that you currently do not have indexes on those fields.

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

CREATE INDEX idxProductRelatedProductId ON cart_product (int);

something along those lines. I don't have access to mysql 5 and havn't used it in a while, but this should be close.