如何优化IN约束查询

Question

下面是我的查询。

SELECT * FROM t1 WHERE t1.record_id IN (
    SELECT  t2.record_id FROM  t2 
        INNER JOIN t3 ON CONCAT(t2.case_number,t2.courtfile_type) = CONCAT(t3.case_number,t3.courtfile_type))

它包含IN约束，从database.Database中提取结果显然需要大量的时间。

如何优化此查询？

Answer 1

试试这个：

使用JOIN

SELECT DISTINCT t1.* 
FROM t1 
INNER JOIN t2 ON t1.record_id = t2.record_id 
INNER JOIN t3 ON t2.case_number = t3.case_number AND t2.courtfile_type = t3.courtfile_type

使用现有

SELECT * 
FROM t1 
WHERE EXISTS (SELECT t2.record_id 
                  FROM  t2 INNER JOIN t3 ON t2.case_number = t3.case_number AND t2.courtfile_type = t3.courtfile_type
                  WHERE t1.record_id = t2.record_id )

使用EXPLAIN关键字检查查询的执行计划，并对表进行适当的索引。