提高查询效率 [英] Increase efficiency in query

问题描述

我有这个查询，我需要对其进行优化以使其运行更快，因为它处理了10,000多个记录.它的速度非常慢，因为要花几个小时才能处理..此查询的作用是，它记录一条记录并检查是否匹配并返回计数，移动到下一个列现在将数据与两个匹配的列都匹配并返回计数，然后转到下一列，检查3列中是否有匹配的数据并返回计数，依此类推，
查询如下，无论如何都可能加快查询速度，这非常好，非常感谢:)非常感谢

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
;

推荐答案

假设您有A，B，C，D，E列，并希望依靠A，B，C ，D

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

然后，您可以在COUNTS_TABLE.CNT上使用比count更快的sum():

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

我对MySql不太强，但是如果它支持"WITH"，它也可以帮助:在原始表上仅应进行一次表扫描.

希望这会有所帮助，
巴勃罗.

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.

这篇关于提高查询效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！