Mysql Query taking forever

[Daxx]

Hi,

we are currently facing a weird problem. We have a query that is pretty simple.

A "select" with 2 "inner join" and in the "where" clause we write "not in (" and put a subquery there.

If we run the query without "not in (subquery)", it seems fine, it reads pretty fast. When we put back the subquery, the query is taking forever.

We made sure that our indexes are good.

What makes this thing weird is that before the end of 2010, this kind of queries were working perfectly and now, it just seem to take forever.

SELECT table1.chainnumber, table1.storenumber, table1.address, table1.city, table1.state, table1.phonenumber, table3.displaytype
FROM table1
INNER JOIN table2
  ON  table2.srid = 
       (SELECT MAX(srid) FROM table2
        WHERE table2.chainnumber = 1
        AND table2.storenumber = 1
        AND table2.contractid = 1)
INNER JOIN table3
  ON table3.displayid = table2.displayid
  AND table3.inactive = '0'
WHERE table1.chainnumber = 1
AND table1.inactive = 0 
and table1.storenumber not in(SELECT storenumber FROM table4 WHERE table4.id = 6812)
ORDER BY table1.chainnumber, table1.storenumber

Let me know if you need more information.

Daxx

[Rabbit]

Unfortunately, I don't know your data and what you are trying to accomplish with your SQL so I can only comment on the SQL itself.

This is the most convoluted SQL I have ever seen. You've turned inner joins into what is essentially a cross join. And your subquery can be expressed using an outer join, which should run faster.