Click here to Skip to main content
11,435,048 members (47,674 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MySQL query
Hi, i have this query where i need to optimize to run faster since it deals with more than 10,000 records. and its pretty slow as in takes hours to process..What this query does is, it takes a record and check for matching ones and return the count, moves to the next column now matches data with both the matching columns and returns the count, and goes to the next column checks for matching data on the 3 columns and returns the count and so on,
the query is as follows and it would be great to speed up this query in anyway possible thank you very much Smile | :) much appreciated
 SELECT t0.*,
 t1.countSameTimeAndLocalIp,
 t2.countSamePort,
 t3.countSameLocalGeo,
 t4.countSameISP,
 t5.countSamefIP,
 t6.countSamefPort,
 t7.countSamefGeo,
 t8.countSameInfection
FROM union_of_outbound_threats t0
LEFT JOIN (
  SELECT `timeStamp`, localIp,
   COUNT(*) AS `countSameTimeAndLocalIp`
  FROM union_of_outbound_threats
  GROUP BY `timeStamp`, localIp
 ) t1 USING (`timeStamp`, localIp)
LEFT JOIN (
  SELECT `timeStamp`, localIp, localport,
   COUNT(*) AS `countSamePort`
  FROM union_of_outbound_threats
  GROUP BY `timeStamp`, localIp, localport
 ) t2 USING (`timeStamp`, localIp, localport)
LEFT JOIN (
  SELECT `timeStamp`, localIp, localport, localgeo,
   COUNT(*) AS `countSameLocalGeo`
  FROM union_of_outbound_threats
  GROUP BY `timeStamp`, localIp, localport, localgeo
 ) t3 USING (`timeStamp`, localIp, localport, localgeo)
LEFT JOIN (
  SELECT `timeStamp`, localIp, localport, localgeo, isp,
   COUNT(*) AS `countSameISP`
  FROM union_of_outbound_threats
  GROUP BY `timeStamp`, localIp, localport, localgeo, isp
 ) t4 USING (`timeStamp`, localIp, localport, localgeo, isp)
LEFT JOIN (
  SELECT `timeStamp`, localIp, localport, localgeo, isp, foreignip,
   COUNT(*) AS `countSamefIP`
  FROM union_of_outbound_threats
  GROUP BY `timeStamp`, localIp, localport, localgeo, isp, foreignip
 ) t5 USING (`timeStamp`, localIp, localport, localgeo, isp, foreignip)
LEFT JOIN (
  SELECT `timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort,
   COUNT(*) AS `countSamefPort`
  FROM union_of_outbound_threats
  GROUP BY `timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort
 ) t6 USING (`timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort)
LEFT JOIN (
  SELECT `timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort, foreigngeo,
   COUNT(*) AS `countSamefGeo`
  FROM union_of_outbound_threats
  GROUP BY `timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort, foreigngeo
 ) t7 USING (`timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort, foreigngeo)
LEFT JOIN (
  SELECT `timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort, foreigngeo, infection,
   COUNT(*) AS `countSameInfection`
  FROM union_of_outbound_threats
  GROUP BY `timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort, foreigngeo, infection
 ) t8 USING (`timeStamp`, localIp, localport, localgeo, isp, foreignip, foreignPort, foreigngeo, infection)
 
order by timestamp, localip
;
Posted 11-Nov-12 23:45pm
dhl_hh521

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Let's say you have columns A, B, C, D, E, and want to count on A, B, C, D
-- This is pseudo-code.
CREATE TABLE COUNTS_TABLE AS 
SELECT A, B, C, D, COUNT(*) CNT FROM MYTABLE GROUP BY A, B, C, D
Then you can use sum(), which is faster than count, on COUNTS_TABLE.CNT:
SELECT A, B, C, SUM(CNT) FROM COUNTS_TABLE GROUP BY A, B, C
 
SELECT A, B, SUM(CNT) FROM COUNTS_TABLE GROUP BY A, B
 
SELECT A, SUM(CNT) FROM COUNTS_TABLE GROUP BY A

I'm not that strong on MySql, but if it supports 'WITH' - it can also help: only one table scan should be done on the original table.

Hope this helps,
Pablo.
  Permalink  
Comments
dhl_hh at 13-Nov-12 22:19pm
   
okey..thank you very much i will see to this :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150428.2 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100