mysql - 优化一个在里面使用一些select的请求

Question

我有一个 SQL 请求从未完成，因为它花费了太多时间，所以我尝试优化它，但暂时找不到。

我的要求是这个:

SELECT id ,field1 
FROM table1
WHERE id NOT IN(
    SELECT id_table1 FROM table2 WHERE id_table1 IS NOT NULL
) 
AND( 
    id IN(
        SELECT id_table1 FROM table3 GROUP BY id_table1
    ) 
    OR id IN(
        SELECT id_table1 FROM table4 GROUP BY id_table1
    )
) 
ORDER BY field1

我认为问题来自于OR，因为当我替换为AND时，它会在几秒钟内完成。

Answer 1

使用显式笛卡尔连接来连接表。

SELECT t1.id, t1.field1 
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id_table1
INNER JOIN table3 t3 ON t1.id = t3.id_table1
INNER JOIN table4 t4 ON t1.id = t4.id_table1
WHERE t1.id != t2.id_table1 
AND t1.id = t3.id_table1  
OR t1.id = t4.id_table1
ORDER BY t1.field1

或者使用存在/不存在的隐式笛卡尔连接

SELECT t1.id, t1.field1 
FROM table1 t1
WHERE NOT EXISTS (SELECT 1
                  FROM table2 t2
                  WHERE t1.id = t2.id_table1 
                  AND t2.id_table1 IS NOT NULL)
AND EXISTS (SELECT 1
            FROM table3 t3 
            WHERE t1.id = t3.id_table1) 
OR EXISTS (SELECT 1
           FROM table4 t4
           WHERE t1.id = t4.id_table1);