mysql 쿼리 : 속도 개선

재귀 쿼리

 WITH RECURSIVE ORG_DATA AS
		(
			-- 1 Dept 
		    SELECT
		        ORG_ID,
		        ORG_NM, 
		        P_ORG_ID,
		        1 AS LV,
		        CONVERT(ORG_ID, VARCHAR(3000)) AS ORG_ID_PATH,
		        CONVERT(ORG_NM, VARCHAR(3000)) AS ORG_NM_PATH
		    FROM
		        organization org 
		    WHERE
		       ORG_ID = 'org_000001'
		       
		    UNION ALL
		    
		    SELECT
		        org.ORG_ID,
		        org.ORG_NM, 
		        org.P_ORG_ID,
		        oda.LV + 1 AS LV,
		        CONCAT(oda.ORG_ID_PATH, '-', org.ORG_ID) AS ORG_ID_PATH,
		        CONCAT(oda.ORG_NM_PATH, '-', org.ORG_NM) AS ORG_NM_PATH
		    FROM
		        organization org  , ORG_DATA oda 
		        where org.P_ORG_ID = oda.ORG_ID  
		)
		SELECT 
			ORG_ID,
	        ORG_NM, 
	        P_ORG_ID,
	        LV,
	        ORG_ID_PATH,
	        ORG_NM_PATH
		FROM ORG_DATA

 

 

가상테이블 이용하는 쿼리

WITH  RLT AS
    (

    select tcpi.UserNum, tcpi.SafeRate, 'tcpi' as ti_ty
     from t_pc_info tcpi
     where 1=1
     <choose>
        <when test="(searchDateFrom != null and searchDateFrom != '') and (searchDateTo == null or searchDateTo == '')">
                <![CDATA[
                AND tcpi.DGNOS_YMD >= STR_TO_DATE(#{searchDateFrom}, '%Y-%m-%d')
                ]]>
            </when>
            <when test="(searchDateTo != null and searchDateTo != '') and (searchDateFrom == null or searchDateFrom == '')">
                <![CDATA[
                AND tcpi.DGNOS_YMD <= STR_TO_DATE(CONCAT(#{searchDateTo}, '23:59:59'), '%Y-%m-%d %H:%i:%s')
                ]]>
            </when>
            <when test="(searchDateFrom != null and searchDateFrom != '') and (searchDateTo != null and searchDateTo != '')">
                <![CDATA[
                AND tcpi.DGNOS_YMD BETWEEN STR_TO_DATE(#{searchDateFrom}, '%Y-%m-%d') AND STR_TO_DATE(CONCAT(#{searchDateTo}, '23:59:59'), '%Y-%m-%d %H:%i:%s')
                ]]>
            </when>
     </choose>

    UNION ALL


    select tcpi2.UserNum, tcpi2.SafeRate, 'tcpi2' as ti_ty
     from t_pc_info2 tcpi2
     where 1=1
     <choose>
        <when test="(searchDateFrom != null and searchDateFrom != '') and (searchDateTo == null or searchDateTo == '')">
            <![CDATA[
            AND tcpi2.DGNOS_YMD >= STR_TO_DATE(#{searchDateFrom}, '%Y-%m-%d')
            ]]>
        </when>
        <when test="(searchDateTo != null and searchDateTo != '') and (searchDateFrom == null or searchDateFrom == '')">
            <![CDATA[
            AND tcpi2.DGNOS_YMD <= STR_TO_DATE(CONCAT(#{searchDateTo}, '23:59:59'), '%Y-%m-%d %H:%i:%s')
            ]]>
        </when>
        <when test="(searchDateFrom != null and searchDateFrom != '') and (searchDateTo != null and searchDateTo != '')">
            <![CDATA[
            AND tcpi2.DGNOS_YMD BETWEEN STR_TO_DATE(#{searchDateFrom}, '%Y-%m-%d') AND STR_TO_DATE(CONCAT(#{searchDateTo}, '23:59:59'), '%Y-%m-%d %H:%i:%s')
            ]]>
        </when>
     </choose>
    )

    select 	A.USER_ID,
        A.USER_NM as name,
        A.ORG_PATH,
         ifnull(max(case when ti_ty = 'tcpi' then SafeRate end), 0) as insafeRate,
         ifnull(max(case when ti_ty = 'tcpi2' then SafeRate end), 0) as exSafeRate
    FROM 
    (
       select
        u.USER_ID,
        u.USER_NM,
        o.ORG_PATH
        from `user` u
            inner join uom_map uom
                on u.USER_ID = uom.USER_ID
            inner join org o
                on o.ORG_ID = uom.ORG_ID 
            <where>
                <if test="authId == 'AUTH_00001'">
                    AND UPPER(o.ORG_ID_PATH) LIKE CONCAT('%', UPPER(#{orgId}), '%')
                </if>
                <if test="searchOrgId != null and searchOrgId != ''">  
                    AND UPPER(o.ORG_ID_PATH) LIKE CONCAT('%',UPPER(#{searchOrgId}), '%')
                </if>   
            </where>
    ) A
    left join RLT on RLT.UserNum = A.USER_ID
    where 1=1
    group by USER_ID, name, ORG_PATH
    ORDER BY SafeRate desc, ORG_PATH
    limit 20