Why using username instead of user id?

[Fajar A. Nugraha]

On Mon, Jan 23, 2012 at 9:45 PM, Morteza Milani <fireboy.mrtz at gmail.com> wrote:
> I couldn't get my answer!

The answer is there. Whether or not you like it is another matter.

>
>> On one of my systems, we use ids. It's somewhat complicated though:
>> - use stored procedures, as I need to use several statements to
>>  achieve the same functionality. It's better than joins (which is very
>>  expensive in mysql cluster)
>>- lookup user id based on username (on an additional table, obviously)
>>- lookup other properties (e.g. radcheck, radreply, etc.) based on the id
>
> The only extra step required is to lookup user id from another table. Then
> we can use it instead of username in all queries.

Correct.

> To achieve this, we can
> set User-Name to that id.

If you mean "use that id as key on further database lookup", then yes, correct.

> Maybe I'm missing something.

You seem to miss the fact that the defaults that FR use are ... well
... defaults. You don't HAVE to use it :)

The changes you want are implementable without the need to touch a
single line of FR's C code. You just need to adjust the sql queries
(e.g. sql/mysql/dialup.conf), and implement the change in the db (e.g.
using stored procedure, modify schema, etc.). Of course you can also
change the code, but my point is you don't HAVE to.

The defaults are simple enough for newbies to impelement and
understand, and good enough for small-medium implementations. If you
are an expert, or have a large implementation, or have special needs,
then you can make your own changes or hire an expert to do so. And if
you think some of the changes might benefit others and you're willing
to share, fell free to write a how-to, edit the wiki, or submit a
patch.

>
> I really want that "live" and "historical" tables implemented.

welcome to the club :)

I believe I've written several times (mostly on freeradius-users) on
the general idea on how to do this. The main problem with my current
implementation is that it's complex and specific to my needs, which I
don't think is well-suited for general use. Creating a generic version
takes time and effort, which I don't have right now.

If you can help or have a better implementation, please contribute. If
not, then wait until someone has the time to write it :)

> I have 35000
> users and I need fetching data from radacct for statistical analysis.> It's a
> heavy table!

statistical analysis is a different thing altogether. It'll STILL be
heavy for the db, even when you split live and historical. The main
benefit that we discussed earlier is that separating those two will
make FR perform faster (e.g. for checking simultaneous-use during user
authentication).

I agree with Alan, if this is your MAIN problem, then index and more
RAM might be a more suitable solution. Also faster disks (e.g. SSD, or
many HDD in raid 10) if you have the budget. Or simply hire a dba to
get more accurate suggestion specific to your particular
implementation.

-- 
Fajar