Mysql subquery run slow on live server but fast on local

Question

I have a pretty simple query, that on my local windows machine takes 0.04 sec but on our live server that is a linux machine it needs 30 seconds!

Anyone familiar with the same problem?

This is the query:

SELECT DISTINCT
s.carId FROM
cardetail_search s WHERE
s.carId IN (SELECT DISTINCT c.carId FROM carid id JOIN car c ON c.carId = id.carId WHERE id.ProductNumber = 'pnumber' OR id.ProductNumber = 'pnumber2')
    AND pictureTableId IN (SELECT DISTINCT pictureTableId FROM cardetail WHERE ProductNumber = 'pnumber' OR ProductNumber = 'pnumber2')

EDIT: any subquery runs slow

SOLUTION: instead of subqueries that use COLUMN IN() when I use INNER JOIN it works as expected. Check the approved answer for the refactored query.

Answer 1

Based on comments, I can think "statistics" on dbs are different. You can try this and see if you obtain better performance (db engine can prepare different plan when using INNER JOIN vs COLUMN IN (...) :

SELECT DISTINCT
s.carId
FROM cardetail_search s 
INNER JOIN (SELECT DISTINCT c.carId FROM carid id 
            INNER JOIN car c ON c.carId = id.carId 
            WHERE id.ProductNumber = 'pnumber' OR id.ProductNumber = 'pnumber2') X ON s.carID = X.carID
INNER JOIN (SELECT DISTINCT pictureTableId FROM cardetail WHERE ProductNumber = 'pnumber' OR ProductNumber = 'pnumber2') Y ON s.pictureTableId = Y.pictureTableId