Slow Queries from poor php script?

[karloska]

Hi there,
I have slow queries caused by this script and I'm afraid we have no idea how to fix it. I enabled the slow_query log to confirm what part of the scripts caused the issue and below is the result in the log. You can see that the rows examined is massive. The database appears to be setup correctly and indexed as it shoud be - when there was a lot less data there is no noticeable speed issue... I did an EXPLAIN in phpmyadmin but the results didnt mean anything to me. Any idea as to how to apporach a fix?

# Time: 110707 13:11:37
# User@Host: freeradius[freeradius] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 0 Rows_examined: 26556600
SET timestamp=1310008297;
SELECT radlookupnasnew.nasid as nasid,radacct.Username as UserName,
Min(radacct.AcctStartTime) AS FirstLogin,
Max(radacct.AcctStartTime) AS LastLogin,
Sum(radacct.AcctInputOctets) AS Upload,
Sum(radacct.AcctOutputOctets)AS Download,
Sum(radacct.AcctInputOctets) + Sum(radacct.AcctOutputOctets)AS TotalData,

count(*) as NumLogins,
case when min(AcctStopTime) = '0000-00-00 00:00:00' then 'Yes' else 'No' end as online,
(select case when radgroupcheck.value is null then 0 else radgroupcheck.value - 

case when min(ra2.AcctStartTime) is null then 0 else sum(ra2.AcctSessionTime) end end from radacct ra2 where ra2.username = radacct.username) as 'SecondsRemaining',


(select case when (min(ra.AcctStartTime) is null or sum(ra.AcctSessionTime) < radgroupcheck.value) then 'No' else 'Yes' end from radacct ra where ra.username = radacct.username) as 'Time_Used_Up',
case when (radchecknew.value is null or radchecknew.value > unix_timestamp(now())) then 'No' else 'Yes' end as 'Expired',

case when (radchecknew.value is null or radchecknew.value > unix_timestamp(now())) then (radchecknew.value - unix_timestamp(now())) else 'Yes' end as 'SecondsRemainingnew'

FROM radacct

LEFT OUTER JOIN (SELECT DISTINCT radlookupnas.nasid, radlookupnas.user FROM radlookupnas) as radlookupnasnew
ON radacct.Username = radlookupnasnew.user

left outer join (SELECT radcheck.UserName, radcheck.Attribute, radcheck.Value from radcheck group by radcheck.username, radcheck.attribute) radgroupcheck 
ON radgroupcheck.username = radacct.username 
AND radgroupcheck.attribute='Max-All-Session'

LEFT OUTER JOIN (SELECT radcheck.UserName, radcheck.Attribute, radcheck.Value from radcheck group by radcheck.username, radcheck.attribute) as radchecknew
ON radacct.Username = radchecknew.UserName
WHERE radchecknew.attribute='Expiration' 

GROUP BY radacct.Username
HAVING radlookupnasnew.nasid='test' and radacct.Username = '' order by max(radacct.AcctStartTime) DESC;

here is the original code:

$query = "SELECT radlookupnasnew.nasid as nasid,radacct.Username as UserName,
Min(radacct.AcctStartTime) AS FirstLogin,
Max(radacct.AcctStartTime) AS LastLogin,
Sum(radacct.AcctInputOctets) AS Upload,
Sum(radacct.AcctOutputOctets)AS Download,
Sum(radacct.AcctInputOctets) + Sum(radacct.AcctOutputOctets)AS TotalData,


count(*) as NumLogins,
case when min(AcctStopTime) = '0000-00-00 00:00:00' then 'Yes' else 'No' end as online,
(select case when radgroupcheck.value is null then 0 else radgroupcheck.value - 


case when min(ra2.AcctStartTime) is null then 0 else sum(ra2.AcctSessionTime) end end from radacct ra2 where ra2.username = radacct.username) as 'SecondsRemaining',


(select case when (min(ra.AcctStartTime) is null or sum(ra.AcctSessionTime) < radgroupcheck.value) then 'No' else 'Yes' end from radacct ra where ra.username = radacct.username) as 'Time_Used_Up',
case when (radchecknew.value is null or radchecknew.value > unix_timestamp(now())) then 'No' else 'Yes' end as 'Expired',

case when (radchecknew.value is null or radchecknew.value > unix_timestamp(now())) then (radchecknew.value - unix_timestamp(now())) else 'Yes' end as 'SecondsRemainingnew'

FROM radacct


LEFT OUTER JOIN (SELECT DISTINCT radlookupnas.nasid, radlookupnas.user FROM radlookupnas) as radlookupnasnew
ON radacct.Username = radlookupnasnew.user

left outer join (SELECT radcheck.UserName, radcheck.Attribute, radcheck.Value from radcheck group by radcheck.username, radcheck.attribute) radgroupcheck 
ON radgroupcheck.username = radacct.username 
AND radgroupcheck.attribute='Max-All-Session'

LEFT OUTER JOIN (SELECT radcheck.UserName, radcheck.Attribute, radcheck.Value from radcheck group by radcheck.username, radcheck.attribute) as radchecknew
ON radacct.Username = radchecknew.UserName
WHERE radchecknew.attribute='Expiration' 



GROUP BY radacct.Username
HAVING radlookupnasnew.nasid='$snasid' and radacct.Username = '$suser' order by max(radacct.AcctStartTime) DESC
"

[coopster]

Welcome to WebmasterWorld, karloska.

A poorly written PHP script is not the cause of a slow query but it is the query itself. Meaning this has nothing to do with PHP. You may consider analyzing the results of your EXPLAIN query and tweak accordingly. Other areas to consider may be the subqueries (subselects) as I have found that they often run much slower than a JOIN or UNION. Other options may be temporary tables but then again, you'll have to play around with your complex queries a bit to see which solution will offer the best performance.

[httpwebwitch]

Do you have indexes on these?

radlookupnasnew.user
radlookupnasnew.nasid
radchecknew.UserName
radchecknew.attribute
radcheck.username
radacct.Username
radgroupcheck.username

JOINs and subselects are expensive. Can you denormalize the data in a way so you don't need to do so much joining?

can the queries be cached? or prefetched?

Can you avoid using case/if/else/then/end logic within the query?

[lostdreamer]

You might also want to try to push everything from the HAVING clause to the WHERE clause:

WHERE radchecknew.attribute='Expiration'
GROUP BY radacct.Username
HAVING radlookupnasnew.nasid='$snasid' and radacct.Username = '$suser' 

becomes

WHERE radchecknew.attribute='Expiration' AND radlookupnasnew.nasid='$snasid' and radacct.Username = '$suser' 
GROUP BY radacct.Username

HAVING will loop over ALL RETURNED ROWS to check which one matches, WHERE will simply disregard them...

[karloska]

"A poorly written PHP script is not the cause of a slow query but it is the query itself."

Argh I see, thanks for the clarification

"push everything from the HAVING clause to the WHERE clause"

Wow you are a legend! I made this one change and now page load appears to be way faster on one of the scripts. I will keep testing and see how we go, thanks alot.