Solved

Suggestions needed to speed up MySQL query

Posted on 2011-03-21
1
292 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 40

Accepted Solution

by:
Sharath earned 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
html input clean up 3 56
Duplicating MySQL Table columns 5 40
How to send multiple emails at the same time in PHP 12 61
Insert values are dynamic 11 42
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 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