mysql - 我该如何修复这个sql查询？

Question

在执行以下查询时遇到“查询期间与 MySQL 服务器失去连接”错误。我该如何解决？

SELECT * 
FROM firmalar,
     musterisahipleri,
     notlar 
WHERE firmalar.firmaID NOT IN (
       SELECT m2.firmaID 
       FROM notlar AS n2,
            musterisahipleri AS m2
       WHERE n2.eklemeTarihi > '2013-03-24'
   ) 
   AND musterisahipleri.firmaID = firmalar.firmaID 
   AND notlar.ilgiliID = musterisahipleri.userID;

谢谢你的帮助

Answer 1

您正在超时，因为您使用的是低效的嵌套子查询。

这会表现得更好:

编辑:根据您最后的评论，此查询将返回您 firmalar 记录，这些记录自“2013-03-24”以来没有添加 notlar 记录...然后再次将这些结果加入 musterisahipleri 和 notlar 以获得相关的代表和注释(如果适用)

SELECT *
FROM (
    SELECT f.* 
    FROM firmalar AS f
    LEFT JOIN musterisahipleri AS m
        ON m.firmaID = f.firmaID
    LEFT JOIN notlar AS n
        ON n.ilgiliID = m.userID
        AND n.eklemeTarihi > '2013-03-24'
    GROUP BY f.firmaID
    HAVING MAX(n.ilgiliID) IS NULL
) AS f
    LEFT JOIN musterisahipleri AS m
        ON m.firmaID = f.firmaID
    LEFT JOIN notlar AS n
        ON n.ilgiliID = m.userID

您还应该确保您在要加入的列上有索引，例如

ALTER TABLE firmalar ADD INDEX (firmaID);
ALTER TABLE musterisahipleri ADD INDEX (firmaID);
ALTER TABLE musterisahipleri ADD INDEX (userID);
ALTER TABLE notlar ADD INDEX (ilgiliID);