Increase efficiency in query

See more:

MySQL

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 :) 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
;