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.