CODEWITHSUNDEEP
phpmysqlmariadb
Josh
Josh

Reputation: 13

Select MAX() of Multiple Columns in MySQL

I currently have a massive query (using PHP and MySQL) and I'm not sure how to optimize it in order to reduce its massive load of 18-25 seconds. I've looked at previous answers and have found that they all sort of write a similarly large amount of code resulting in a similar script execution time and so I'd like to see if anyone would have any alternative ideas as opposed to the ones already posed:

TLDR; I need to select the maximum column for each analytics ID and this takes on a massive load and so i need to reduce it somehow

$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";

This is taking data from a massive table and so i don't expect it to take 0 seconds however any time reduction would be fine rather than ~21 seconds.

Upvotes: 1

Views: 77

Answers (2)

Rick James
Rick James

Reputation: 142296

Why not 

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";

You should have some index starting with bid. This might be better:

INDEX(bid, ip, country_code, country)

Upvotes: 1

M0rtiis
M0rtiis

Reputation: 3774

You should have a composite KEY on (bid, ip). If you have KEY on (bid) then add (or replace it with) (bid, ip)

Upvotes: 0

Related Questions