mysql - Sum() 减慢了我的查询速度

Question

我在 case 语句中使用 sum() 。但这会减慢我的查询速度。有没有其他办法解决这个问题。这是查询。请帮助我。

SELECT (SUM(PRI_INS_AGING) + SUM(SEC_INS_AGING) + SUM(TER_INS_AGING)) AS INS_AGING,SUM(PAT_AGING) AS PAT_AGING FROM
                (SELECT 
        CASE WHEN L.RESP_PARTY =1 AND VP.STATUS IN(3,5) AND VP.PRIMARY_PAID =0 AND VP.PRIMARY_PENDING >0 AND C.PRIMARY_PAYER_ID >0 AND C.HIDEN=0 THEN SUM(L.AMOUNT) ELSE 0 END AS PRI_INS_AGING,
        CASE WHEN L.RESP_PARTY =2 AND VP.STATUS IN(6,7,5) AND VP.SECONDARY_PAID =0 AND VP.SECONDARY_PENDING >0 AND VP.PRIMARY_PENDING <=0 AND C.SECONDARY_PAYER_ID >0 AND C.HIDEN=0 THEN SUM(L.AMOUNT) ELSE 0 END AS SEC_INS_AGING,
        CASE WHEN L.RESP_PARTY =3 AND VP.STATUS IN(8,9,5) AND VP.TERTIARY_PAID =0 AND VP.TERTIARY_PENDING >0 AND VP.PRIMARY_PENDING <=0 AND VP.SECONDARY_PENDING <=0 AND C.TERTIARY_PAYER_ID >0 AND C.HIDEN=0 THEN SUM(L.AMOUNT) ELSE 0 END AS TER_INS_AGING,
        CASE WHEN L.RESP_PARTY =4 THEN SUM(L.AMOUNT) ELSE 0 END AS PAT_AGING
        FROM VISIT_PROCEDURE VP
        JOIN CLAIM C 
        ON (C.CLAIM_ID = VP.CLAIM_ID AND C.CLINIC_ID = VP.CLINIC_ID)
        JOIN LEDGER L
        ON (L.CLAIM_ID = L.CLAIM_ID AND VP.CLINIC_ID = L.CLINIC_ID)
        WHERE C.CLINIC_ID = 34847 AND L.TYPE IN(1,8,9,10,11) AND L.ACTIVE=1 
        GROUP BY VP.PROCEDURE_ID,L.TYPE,L.RESP_PARTY,L.ACTIVE)T1

谢谢苏尼尔

Answer 1

无需额外剖析您的 SQL，即可查看 LEDGER 上的连接...您让它对相同的值执行 CLAIM_ID...

JOIN LEDGER L   ON L.CLAIM_ID = L.CLAIM_ID AND VP.CLINIC_ID = L.CLINIC_ID

“L.Claim_ID”是否应该加入“VP.Claim_ID”？？？或者其他什么？

好的，所以用了一点时间，我想出了这个......我会交换一些查询。此外，请确保您的 CLAIM 表上有 Clinic_ID 和 Hiden 的索引。此外，您的内部查询正在分解保险 claim 不同部分的总和，但除了在外部对它们求和之外，您没有对它们做任何其他事情。对于给定的条件，我将更改为仅在外部求和一次

SELECT STRAIGHT_JOIN
      SUM( IF(   L.RESP_PARTY = 1 
             AND VP.STATUS IN(3,5) 
             AND VP.PRIMARY_PAID = 0 
             AND VP.PRIMARY_PENDING > 0 
             AND C.PRIMARY_PAYER_ID > 0, L.AMOUNT, 0 )

           +
           IF(   L.RESP_PARTY = 2 
             AND VP.STATUS IN(6,7,5) 
             AND VP.SECONDARY_PAID = 0 
             AND VP.SECONDARY_PENDING > 0 
             AND VP.PRIMARY_PENDING <= 0 
             AND C.SECONDARY_PAYER_ID > 0, L.AMOUNT, 0 )

           +
           IF(   L.RESP_PARTY = 3 
             AND VP.STATUS IN(8,9,5) 
             AND VP.TERTIARY_PAID = 0 
             AND VP.TERTIARY_PENDING > 0 
             AND VP.PRIMARY_PENDING <= 0 
             AND VP.SECONDARY_PENDING <= 0 
             AND C.TERTIARY_PAYER_ID > 0, L.AMOUNT, 0 ) ) as INS_AGING,

      SUM( IF( L.RESP_PARTY = 4, L.AMOUNT, 0 )) as PAT_AGING

   FROM 
      CLAIM C
         JOIN VISIT_PROCEDURE VP
            ON C.CLAIM_ID = VP.CLAIM_ID
           AND C.CLINIC_ID = VP.CLINIC_ID

             JOIN LEDGER L
                ON VP.CLINIC_ID = L.CLINIC_ID
               AND VP.CLAIM_ID = L.CLAIM_ID
               AND L.TYPE IN ( 1, 8, 9, 10, 11 ) 
               AND L.ACTIVE = 1 
   WHERE 
          C.CLINIC_ID = 34847 
      AND C.HIDEN = 0