Solved

Suggestions needed to speed up MySQL query

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Update Query 23 93
Coldfusion- Create and save form elements in Database 7 61
Need help with a query 6 67
showing numeric numbers 2 33
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now