i have a select that seem to be taking too long for what it is, and after
looking over the documents i can't seem to find a better way.
probably some things that would help:
-on a datetime field, is there a way to look at everything from a month that
still uses the index.
-is there a way to cause a select to work more in the background and not kill
the server while its working (it may take longer, but atleast other queries
will keep working)
select logon_id from customers where Limited_Access = 1;
....2100 rows in set (0.41 sec)
select Username, sum(AcctSessionTime)/3600 as hrs from customers left join
radacct on (customers.logon_id = radacct.username)
where Limited_Access=1 and
AcctStopTime > '2002-12-31' and AcctStopTime < '2003-02-01'
group by Username
having hrs > 65
order by Username;
.... after about 8 minutes, killed the process, but it should have about 2100
records as well, and getting a few complaints about the data server not
responding from one of the main programs that is using it.
explain says:
| table | type | possible_keys | key | key_len | ref
|
| rows | Extra |
+-----------+------+----------------+----------------+---------+-------------
-------+------+-----------------------------+
| customers | ref | Limited_Access | Limited_Access | 2 | const
|
| 2169 | where used; Using temporary |
|
| radacct | ref | UserName | UserName | 64 |
customers.LOGON_ID | 3 | where used
CREATE TABLE customers (
### trimmed the extra fields out of this one
ID int(11) NOT NULL auto_increment,
LOGON_ID varchar(20) default NULL,
Limited_Access tinyint(4) default NULL,
PRIMARY KEY (ID),
KEY Limited_Access (Limited_Access)
) TYPE=MyISAM;
CREATE TABLE radacct (
RadAcctId bigint(21) NOT NULL auto_increment,
AcctSessionId varchar(32) NOT NULL default '',
AcctUniqueId varchar(32) NOT NULL default '',
UserName varchar(64) NOT NULL default '',
Realm varchar(64) default '',
NASIPAddress varchar(15) NOT NULL default '',
NASPortId int(12) default NULL,
NASPortType varchar(32) default NULL,
AcctStartTime datetime NOT NULL default '0000-00-00 00:00:00',
AcctStopTime datetime NOT NULL default '0000-00-00 00:00:00',
AcctSessionTime int(12) default NULL,
AcctAuthentic varchar(32) default NULL,
ConnectInfo_start varchar(32) default NULL,
ConnectInfo_stop varchar(32) default NULL,
AcctInputOctets int(12) default NULL,
AcctOutputOctets int(12) default NULL,
CalledStationId varchar(10) NOT NULL default '',
CallingStationId varchar(10) NOT NULL default '',
AcctTerminateCause varchar(32) NOT NULL default '',
ServiceType varchar(32) default NULL,
FramedProtocol varchar(32) default NULL,
FramedIPAddress varchar(15) NOT NULL default '',
AcctStartDelay int(12) default NULL,
AcctStopDelay int(12) default NULL,
PRIMARY KEY (RadAcctId),
KEY UserName (UserName),
KEY FramedIPAddress (FramedIPAddress),
KEY AcctSessionId (AcctSessionId),
KEY AcctUniqueId (AcctUniqueId),
KEY AcctStartTime (AcctStartTime),
KEY AcctStopTime (AcctStopTime),
KEY NASIPAddress (NASIPAddress)
) TYPE=MyISAM;
there are about 780,000 records in the radacct table, about 1/2 of that is
this month
--
mysql, sql, query, sql, sql, sql
---------------------------------------------------------------------
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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
