我目前有一个庞大的查询(使用PHP和MySQL),我不确定如何优化它,以减少其18-25秒的巨大负载。我看过以前的答案,发现他们都写了类似的大量代码,导致类似的脚本执行时间,所以我想看看是否有人会有任何替代的想法,而不是那些已经提出的:
TLDR;我需要为每个分析ID选择最大列,这需要大量的负载,所以我需要以某种方式减少它
$qry = "SELECT DISTINCT ip,country_code,country,
(SELECT MAX(region) FROM analytics RegOne WHERE bid='$id' AND RegOne.ip= IPOne.ip) AS region,
(SELECT MAX(city) FROM analytics CitOne WHERE bid='$id' AND CitOne.ip= IPOne.ip) AS city,
(SELECT MAX(os) FROM analytics OSOne WHERE bid='$id' AND OSOne.ip= IPOne.ip) AS os,
(SELECT MIN(browser) FROM analytics BroOne WHERE bid='$id' AND BroOne.ip= IPOne.ip) AS browser,
(SELECT MIN(resolution) FROM analytics ResOne WHERE bid='$id' AND ResOne.ip= IPOne.ip) AS resolution,
(SELECT MAX(timestamp) FROM analytics DateOne WHERE bid='$id' AND DateOne.ip= IPOne.ip) AS LatestDate
FROM analytics AS IPOne
WHERE bid='$id'
ORDER BY LatestDate DESC
LIMIT 15 OFFSET $offset";
这是从一个巨大的表中获取数据,所以我不希望它花费0秒,但是任何时间减少都会很好,而不是~21秒。
为什么不
SELECT ip, country_code, country,
MAX(region) as region,
MAX...
MAX(timestamp) as LatestDate
FROM analytics
WHERE bid = '$id'
GROUP BY ip, country_code, country
ORDER BY LatestDate DESC
LIMIT 15 OFFSET $offset";
您应该有一些以bid
开头的索引。这样可能更好:
INDEX(bid, ip, country_code, country)
您应该在(bid, ip)
上有一个复合KEY。如果您在(bid)
上有KEY,那么添加(或替换为)(bid, ip)