[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Suggestions needed to speed up MySQL query

Posted on 2011-03-21
1
Medium Priority
?
302 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:cassie5643
1 Comment
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35183555
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 16 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question