Hi,
On 2012-09-21 10:55 AM, Rich Graves wrote:
> 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;
I think you are right about this. That would return the most recent
session info. I am willing to make the change if you can confirm the
output data is the same.
>
> 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.
>
Generating time range on the code side is feasible, but I think the
usage of the data functions is much easier. Let the SQL deal with the
data stuff.
Btw, the difference between last 7 days and this week is simple:
- Last 7 days = From last Friday to this Friday (Sliding Window)
- This week = From Monday to this Friday. (Fixed width)
> 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 = ?;
mysql> describe radacct;
+----------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+----------------------+-------------+------+-----+---------+----------------+
| username | varchar(64) | NO | MUL | |
|
Username does have an index.
Thanks!
--
Francois Gaudreault, ing. jr
[email protected] :: +1.514.447.4918 (x130) :: www.inverse.ca
Inverse inc. :: Leaders behind SOGo (www.sogo.nu) and PacketFence
(www.packetfence.org)
------------------------------------------------------------------------------
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
