【Mysql】【サブクエリ-left join書き換え】フロントエンドSQLチューニング

関連データはすでに脱感処理されている

--  SQL
SELECT * FROM (SELECT a.*, (SELECT sum(CASE WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND b.KEY_NAME = d.TYPE_NAME) THEN 1 ELSE 0 END) FROM tableAAA_detail b, tableBBB c, tableBBB_detail d WHERE c.TYPE_NAME = 'tableAAA' AND c.ID = d.TYPE_ID AND b.ANNO_ID = a.ANNO_ID AND b.RECORD_ID = a.RECORD_ID AND (a.ACTIVE_FLG <> 1 OR a.ACTIVE_FLG IS NULL) AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL) AND b.KEY_NAME = d.TYPE_NAME) AS checkFlg FROM tableAAA a WHERE (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1) ORDER BY a.ACTIVE_FLG ASC, CASE WHEN checkFlg > 0 THEN 0 ELSE 1 END ASC) p ;


-- Join  （   ）
SELECT a.*,sum(CASE WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND b.KEY_NAME = d.TYPE_NAME) THEN 1 ELSE 0 END) AS checkFlg FROM tableAAA a join tableAAA_detail b on ( b.ANNO_ID = a.ANNO_ID AND b.RECORD_ID = a.RECORD_ID) join tableBBB_detail d on (b.KEY_NAME = d.TYPE_NAME) join tableBBB c on (c.ID = d.TYPE_ID) WHERE (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1) AND (a.ACTIVE_FLG <> 1 OR a.ACTIVE_FLG IS NULL) AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL) and c.TYPE_NAME = 'tableAAA' group by a.ANNO_ID, a.RECORD_ID /* ORDER BY a.ACTIVE_FLG ASC, CASE WHEN checkFlg > 0 THEN 0 ELSE 1 END ASC */ --left join    SELECT a.*,b.checkFlg from tableAAA a left join ( SELECT b.ANNO_ID,b.RECORD_ID,sum(CASE WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND b.KEY_NAME = d.TYPE_NAME) THEN 1 ELSE 0 END) AS checkFlg FROM tableAAA_detail b, tableBBB c, tableBBB_detail d WHERE c.TYPE_NAME = 'tableAAA' AND c.ID = d.TYPE_ID AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL) AND b.KEY_NAME = d.TYPE_NAME group by b.ANNO_ID,b.RECORD_ID ) b on (b.ANNO_ID = a.ANNO_ID AND b.RECORD_ID = a.RECORD_ID) where (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1) ORDER BY a.ACTIVE_FLG ASC, checkFlg desc