Change your query to use BETWEEN rather that <= and >=. --ja
On Tue, 11 Nov 2003, Michael Shuler wrote: > OK, I give up. To anyone out there who can help me, please explain why this > query runs slower than dirt. The table has about 1,300,000 records in it, > which is not supposed to be a big deal for MySQL to deal with. I have tried > it with MyISAM and then changed it to InnoDB which made it even slower but > at least the rest of my queries can continue and not be blocked. This query > takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC. In my opinion it > should be 10x faster than that at the very least. > > This table is used for RADIUS accounting, all I want to do is find the peak > utilization port utilization for the day. The only way I have figured out > how to do this is take samples every 5 min and store the highest one. Here > is the table: > > CREATE TABLE `ServiceRADIUSAccounting` ( > `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, > `XmitSpeed` varchar(6) default NULL, > `RecvSpeed` varchar(6) default NULL, > `AcctInputOctets` int(12) default NULL, > `AcctOutputOctets` int(12) default NULL, > `CalledStationId` varchar(11) NOT NULL default '', > `CallingStationId` varchar(11) 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`), > KEY `Realm` (`Realm`), > KEY `RealmAndStart` (`Realm`,`AcctStartTime`) > ) TYPE=InnoDB AUTO_INCREMENT=4468368 ; > > > And here is the query: > > SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm = > 'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00' AND AcctStopTime > >= '2003-11-11 15:30:00') > > When I do an explain I get: > table type possible_keys > key key_len ref rows Extra > ServiceRADIUSAccounting ref > AcctStartTime,AcctStopTime,Realm,RealmAndStart RealmAndStart 65 const > 73394 Using where > > Perhapse my InnoDB file needs to be "optimized" if such a thing exists. I > don't know why this takes so long but I can definitly use some help. > Thanks! > > ---------------------------------------- > > Michael Shuler > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
