No reason for the varchar so I guess I can change it to char if that will help. I have tried many different settings in the my.cnf file for inno db but have seen little improvement. Any thoughts there?
-----Original Message----- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 4:02 PM To: Michael Shuler Cc: [EMAIL PROTECTED] Subject: Re: Dirt Slow Query On Datetime Range The first thing I noticed is that you are using varchar instead of char, why? Unless you have a variable length field like text or blob, you can and should use char. As far as I know, you can't "optimize" InnoDB tables, but you can optimize MyISAM using the analyze command as I recall. Next, have you played with your my.cnf settings? I assume you have, but it's a question that must be asked. On Tuesday, November 11, 2003, at 04:44 PM, 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] > > > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
