php - 在 MySQL 中选择多列的 MAX()

Question

我目前有一个庞大的查询(使用 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 秒。

Answer 1

为什么不

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)