cassie5643
asked on
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
#####
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.