Search code examples
mysqlsqlfreeradiusradius

Radius SQL query - optimize

I am no SQL guru at all, but I have an SQL query that has been used automated on a small FreeRadius server. Now we want to use it on a bigger server, but i noticed the query takes a long time, and utilizes CPU 100% for about two minutes...

Does anyone have an idea on how to optimize the query? The goal is to find if a user has more than one active login, and get info on the oldest.

We can also accept more results, and do the sorting in PHP, to reduce the load on the radius-database.

Here is the query we use today:

  SELECT username, acctstarttime, acctsessionid, nasipaddress, framedipaddress
        FROM radacct
        WHERE username IN (
            SELECT username
            FROM radacct
            WHERE acctstoptime IS NULL
            GROUP BY username
            HAVING COUNT(username) > 1
        )
        AND acctstoptime IS NULL
        GROUP BY username
        ORDER BY username, acctstarttime ASC

Regards

Kim

Solution

  • I think we can just make a single pass over the entire radacct table:

    SELECT username, MIN(acctstarttime) AS min_start_time
FROM radacct
WHERE acctstoptime IS NULL
GROUP BY username
HAVING COUNT(*) >= 2
LIMIT 0, 30;

    This would return only users having two or more active accounts. And it would also return the latest acctstarttime value for each user.