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]
