I'm working on the following select statement and have a few problems:
select if(contact.comp_name, contact.comp_name, concat(contact.first_name,
' ', contact.last_name)) as name, if(substring(radacct.username,1,4) =
'brow', lcase(trim(leading concat(substring_index(radacct.username,'-',1),
'-') from radacct.username)), lcase(radacct.username)) as login,
account.activated, radacct.acctstarttime from users, contact, account,
radius.radacct where radacct.acctstarttime and radacct.acctstoptime and
if(substring(radacct.username,1,4) = 'brow', lcase(trim(leading
concat(substring_index(radacct.username,'-',1), '-') from
radacct.username)), lcase(radacct.username)) = users.username and
account.id = users.id and users.owner_id = contact.id group by login order
by login, acctstarttime desc;
First, the query takes almost a minute to run. Explain shows this:
| radacct | ALL | NULL | NULL | NULL | NULL | 470127 | where
|used; Using temporary; Using filesort |
| users | ref | PRIMARY,username | username | 30 | func | 10 | where
|used |
| account | eq_ref | PRIMARY | PRIMARY | 3 | users.id | 1 |
| |
| contact | eq_ref | PRIMARY | PRIMARY | 3 | users.owner_id | 1 |
| |
Second, the acctstarttime needs to be the most recent time, and the query
returns the oldest. In case it's simpler to start from scratch instead of
patching my attempt, here's what I'm working with:
contact table: if the comp_name field holds data (Company name), return that.
Otherwise, return "first_name last_name".
radacct table: username may start with a prefix (always 8 characters in
this brow***- format). Return just the portion after the prefix if it
exists.
return the lastest dial-up start time.
account table: return the activation date for the account.
My attempts with the various join methods have failed miserably. Any
ideas on what I can do to optimize this? If not, I definitely need to
have the latest dial-in times (acctstarttime). I remember seeing
something on list about sorting on 2 columns, but can't remember enough of
the details to find it in the archives.
Thanks for any assistance you can offer.
--
===============================================
Kip Turk phone: 915.234.5678
Systems Administrator or 800.695.9016
Killer of Spam/Writer of Code/Penguin Proponent
West Central Net fax: 915.656.0071
===============================================
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
