Suggestions needed to speed up MySQL query

Suggestions needed to speed up MySQL query... please see below... thanks


#####

SELECT ACCESS_ROUTER, ASN, ASN_NAME, IP_SUM

## Union all four categories under ALLGROUPS
FROM (

## TOP10
(
SELECT "1" AR_TYPE, ACCESS_ROUTER, ASN, ASN_NAME, SUM(IP_COUNT) IP_SUM
FROM LATEST_ASSESSMENTS2 A

JOIN (
      SELECT AGENCY, DATA_DATE, IF(AR_HOSTNAME='', ACCESS_IP_OCT, AR_HOSTNAME) ACCESS_ROUTER, ASN, ASN_NAME, IP_COUNT
      FROM AR_ROLLUP AR
      JOIN AGENCY_LOOKUP AL
      ON AR.INFRA_ID = AL.INFRA_ID
      WHERE ASN NOT IN (0, -2, -5)
)B

ON A.AGENCY = B.AGENCY AND A.DATA_DATE = B.DATA_DATE
GROUP BY ACCESS_ROUTER
ORDER BY IP_SUM DESC
LIMIT 0, 10
)

UNION

## OTHER
(
SELECT AR_TYPE, "OTHER" ACCESS_ROUTER, "0" ASN, "NA" ASN_NAME, SUM(IP_SUM) IP_SUM
FROM (
      SELECT "2" AR_TYPE, ACCESS_ROUTER, ASN, ASN_NAME, SUM(IP_COUNT) IP_SUM
      FROM LATEST_ASSESSMENTS2 A
      JOIN (
            SELECT AGENCY, DATA_DATE, IF(AR_HOSTNAME='', ACCESS_IP_OCT, AR_HOSTNAME) ACCESS_ROUTER, ASN, ASN_NAME, IP_COUNT
            FROM AR_ROLLUP AR
            JOIN AGENCY_LOOKUP AL
            ON AR.INFRA_ID = AL.INFRA_ID
            WHERE ASN NOT IN (0, -2, -5)
      ) B
      ON A.AGENCY = B.AGENCY AND A.DATA_DATE = B.DATA_DATE
      
      GROUP BY ACCESS_ROUTER
      ORDER BY IP_SUM DESC
      LIMIT 11, 100000
) OTHER
GROUP BY AR_TYPE
)

UNION

## UNION OF UNKNOWN & UNADVERTISED
(
SELECT AR_TYPE, ACCESS_ROUTER, ASN, ASN_NAME, SUM(IP_SUM) IP_SUM
FROM LATEST_ASSESSMENTS2 A

JOIN (

      # UNKNOWN
      (
      SELECT AGENCY, DATA_DATE, "3" AR_TYPE, "UNKNOWN" ACCESS_ROUTER, "0" ASN, "NA" ASN_NAME, SUM(IP_COUNT) IP_SUM
      FROM AR_VALIDATED_FINAL AVF
      JOIN AGENCY_LOOKUP AL ON AVF.INFRA_ID = AL.INFRA_ID
      WHERE ACCESS_IP_OCT='' AND DEST_ASN<>-2
      GROUP BY AGENCY, DATA_DATE
      )

      UNION
      # UNADVERTISED
      (
      SELECT AGENCY, DATA_DATE, "4" AR_TYPE, "UNADVERTISED" ACCESS_ROUTER, "0" ASN, "NA" ASN_NAME, SUM(IP_COUNT) IP_SUM
      FROM AR_VALIDATED_FINAL AVF
      JOIN AGENCY_LOOKUP AL ON AVF.INFRA_ID = AL.INFRA_ID
      WHERE DEST_ASN=-2
      GROUP BY AGENCY, DATA_DATE
      )

) B

ON A.AGENCY = B.AGENCY AND A.DATA_DATE = B.DATA_DATE
GROUP BY AR_TYPE
)
) ALLGROUPS

ORDER BY AR_TYPE ASC, IP_SUM DESC
cassie5643Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
Each sub-query returns different record set from other sub-query as you have AR_TYPE hardcoded as 1,2,3,4 in each of sub-query. Hence I would suggest you to change UNION to UNION ALL.
SELECT ACCESS_ROUTER, 
         ASN, 
         ASN_NAME, 
         IP_SUM 
    FROM ((  SELECT "1"           AR_TYPE, 
                    ACCESS_ROUTER, 
                    ASN, 
                    ASN_NAME, 
                    SUM(IP_COUNT) IP_SUM 
               FROM LATEST_ASSESSMENTS2 A 
                    JOIN (SELECT AGENCY, 
                                 DATA_DATE, 
                                 IF(AR_HOSTNAME = '',ACCESS_IP_OCT,AR_HOSTNAME) ACCESS_ROUTER,
                                 ASN, 
                                 ASN_NAME, 
                                 IP_COUNT 
                            FROM AR_ROLLUP AR 
                                 JOIN AGENCY_LOOKUP AL 
                                   ON AR.INFRA_ID = AL.INFRA_ID 
                           WHERE ASN NOT IN (0,-2,-5)) B 
                      ON A.AGENCY = B.AGENCY 
                         AND A.DATA_DATE = B.DATA_DATE 
           GROUP BY ACCESS_ROUTER 
           ORDER BY IP_SUM DESC 
              LIMIT 0,10) 
          UNION ALL
          (  SELECT AR_TYPE, 
                    "OTHER"     ACCESS_ROUTER, 
                    "0"         ASN, 
                    "NA"        ASN_NAME, 
                    SUM(IP_SUM) IP_SUM 
               FROM (  SELECT "2"           AR_TYPE, 
                              ACCESS_ROUTER, 
                              ASN, 
                              ASN_NAME, 
                              SUM(IP_COUNT) IP_SUM 
                         FROM LATEST_ASSESSMENTS2 A 
                              JOIN (SELECT AGENCY, 
                                           DATA_DATE, 
                                           IF(AR_HOSTNAME = '',ACCESS_IP_OCT,AR_HOSTNAME) ACCESS_ROUTER,
                                           ASN, 
                                           ASN_NAME, 
                                           IP_COUNT 
                                      FROM AR_ROLLUP AR 
                                           JOIN AGENCY_LOOKUP AL 
                                             ON AR.INFRA_ID = AL.INFRA_ID 
                                     WHERE ASN NOT IN (0,-2,-5)) B 
                                ON A.AGENCY = B.AGENCY 
                                   AND A.DATA_DATE = B.DATA_DATE 
                     GROUP BY ACCESS_ROUTER 
                     ORDER BY IP_SUM DESC 
                        LIMIT 11,100000) OTHER 
           GROUP BY AR_TYPE) 
          UNION ALL
          (  SELECT AR_TYPE, 
                    ACCESS_ROUTER, 
                    ASN, 
                    ASN_NAME, 
                    SUM(IP_SUM) IP_SUM 
               FROM LATEST_ASSESSMENTS2 A 
                    JOIN ((  SELECT AGENCY, 
                                    DATA_DATE, 
                                    "3"           AR_TYPE, 
                                    "UNKNOWN"     ACCESS_ROUTER, 
                                    "0"           ASN, 
                                    "NA"          ASN_NAME, 
                                    SUM(IP_COUNT) IP_SUM 
                               FROM AR_VALIDATED_FINAL AVF 
                                    JOIN AGENCY_LOOKUP AL 
                                      ON AVF.INFRA_ID = AL.INFRA_ID 
                              WHERE ACCESS_IP_OCT = '' 
                                    AND DEST_ASN <>- 2 
                           GROUP BY AGENCY, 
                                    DATA_DATE) 
                          UNION ALL
                          (  SELECT AGENCY, 
                                    DATA_DATE, 
                                    "4"            AR_TYPE, 
                                    "UNADVERTISED" ACCESS_ROUTER, 
                                    "0"            ASN, 
                                    "NA"           ASN_NAME, 
                                    SUM(IP_COUNT)  IP_SUM 
                               FROM AR_VALIDATED_FINAL AVF 
                                    JOIN AGENCY_LOOKUP AL 
                                      ON AVF.INFRA_ID = AL.INFRA_ID 
                              WHERE DEST_ASN =- 2 
                           GROUP BY AGENCY, 
                                    DATA_DATE)) B 
                      ON A.AGENCY = B.AGENCY 
                         AND A.DATA_DATE = B.DATA_DATE 
           GROUP BY AR_TYPE)) ALLGROUPS 
ORDER BY AR_TYPE ASC, 
         IP_SUM DESC

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.