?
Solved

Suggestions needed to speed up MySQL query

Posted on 2011-03-21
1
Medium Priority
?
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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