performance issue with VARCHAR index

[Hamed Nik]

Hi,

I'm having very slow query times on a relatively simple query. Here are the details:

My hardware is a Intel quad core 2.4 with 4GB RAM...

Mysql Distrib 5.0.77...

The table has about 10 million rows, size is about 6.6GB, here is the table DDL.

CREATE TABLE `radstop` (
`RadAcctId` bigint(21) NOT NULL auto_increment,
`AcctSessionId` varchar(32) NOT NULL default '',
`BuyRateCode` int(11) default NULL,
`UserName` varchar(64) NOT NULL default '',
`dstuser` varchar(64) default NULL,
`h323SetupTime` varchar(34) default NULL,
`h323ConnectTime` varchar(34) default NULL,
`h323DisconnectTime` varchar(34) default NULL,
`AcctSessionTime` int(12) default NULL,
`CalledStationId` varchar(28) NOT NULL,
`CallingStationId` varchar(20) NOT NULL default '',
`h323DisconnectCause` char(2) default NULL,
`srcrtpaddress` varchar(25) default NULL,
`destrtpaddress` varchar(25) default NULL,
`h323RemoteAddress` varchar(15) NOT NULL default '',
`h323VoiceQuality` int(12) default NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`routeretries` int(2) default NULL,
`srcnumberout` varchar(28) default NULL,
`dstnumberout` varchar(35) default NULL,
`h323GwId` varchar(15) default NULL,
`srcfaststart` int(1) default NULL,
`dstfaststart` int(1) default NULL,
`srctunneling` int(1) default NULL,
`dsttunneling` int(1) default NULL,
`srccodec` varchar(124) default NULL,
`dstcodec` varchar(30) default NULL,
`pddtime` int(4) default NULL,
`scdtime` int(4) default NULL,
`SellRateCode` int(11) default NULL,
PRIMARY KEY (`RadAcctId`),
KEY `UserName` (`UserName`),
KEY `AcctSessionId` (`AcctSessionId`),
KEY `date` (`date`),
KEY `AcctSessionTime` (`AcctSessionTime`),
KEY `DestUser` (`dstuser`)
) ENGINE=InnoDB AUTO_INCREMENT=30022444 DEFAULT CHARSET=latin1;


The query and the explain is:

mysql> EXPLAIN EXTENDED
-> select AcctSessionTime,CalledStationId,date from radstop where (AcctSessionTime > 0 AND date >= '20100823000000' AND date <= '20100823235959' AND UserName = 'AAA_Orig' );
+----+-------------+---------+------+-------------------------------+----------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-------------------------------+----------+---------+-------+-------+-------------+
| 1 | SIMPLE | radstop | ref | UserName,date,AcctSessionTime | UserName | 66 | const | 91392 | Using where |
+----+-------------+---------+------+-------------------------------+----------+---------+-------+-------+-------------+

As you can see there is an index on all of the where clause fields.

The query above takes more than 15 minutes to run.

If I remove the UserName field from the where clause, i get much faster response. But the UserName is indexed!

Can you please comment on what I could be doing wrong?



Edited 1 time(s). Last edit at 08/23/2010 06:27PM by Hamed Nik.