For the "Last Session" lookup, I don't think you need the subquery at all. It
can be reduced to:
select
CONCAT(SUBSTRING(callingstationid,1,2),':',SUBSTRING(callingstationid,3,2),':',SUBSTRING(callingstationid,5,2),':',
SUBSTRING(callingstationid,7,2),':',SUBSTRING(callingstationid,9,2),':',SUBSTRING(callingstationid,11,2))
AS mac,username,IF(ISNULL(acctstoptime),'connected','not connected') AS
status,acctstarttime,acctstoptime,FORMAT(acctsessiontime/60,2) AS
acctsessiontime,
nasipaddress,nasportid,nasporttype,acctinputoctets AS
acctoutput,
acctoutputoctets AS
acctinput,(acctinputoctets+acctoutputoctets) AS accttotal,
IF(ISNULL(acctstoptime),'',acctterminatecause) AS
acctterminatecause
FROM radacct WHERE callingstationid = ? ORDER BY acctstarttime DESC
LIMIT 1;
For the acct_bandwidth_{weekly,monthly}_sql queries, I think it would be faster
to search for date ranges rather than pulling YEARWEEK, MONTH (which returns
bad data if you have more than 11 months of radacct data), and YEAR. The range
search will use an index; the functional extract won't. It's too early in the
morning for me to make the distinction between "this week" and "last 7 days,"
but it can be done. The "yearly" query should just scan them all.
If this query is used in a binary fashion, you ought to be able to do a LIMIT 1
here, too, though if username is indexed, there's little if any real difference.
$accounting_statements->{'acct_exist_sql'} = get_db_handle()->prepare(qq[
SELECT COUNT(*) FROM radacct WHERE username = ?;
------------------------------------------------------------------------------
Got visibility?
Most devs has no idea what their production app looks like.
Find out how fast your code is with AppDynamics Lite.
http://ad.doubleclick.net/clk;262219671;13503038;y?
http://info.appdynamics.com/FreeJavaPerformanceDownload.html
_______________________________________________
PacketFence-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/packetfence-users
