Hi Gabriel,
No, the order of the WHERE clause shouldn't have anything to do with how
the query is executed. The only exception may be if MySQL thinks 2
different ways of doing something are of equal cost. In that case it may
choose one or the other depending on how the query is written.
Matt
----- Original Message -----
From: "Gabriel Ricard"
Sent: Wednesday, November 12, 2003 10:00 AM
Subject: Re: Dirt Slow Query On Datetime Range...the saga continues
> In order to make sure of a multi-column index, you have to order the
> WHERE clauses in the same order as the columns in the index. Since you
> query Realm first, then AcctStartTime, then AcctStopTime, MySQL would
> use an index on those columns in that order. You can either add a
> differently ordered index of the same columns or not, just make sure
> your query has the columns in the right order.
>
> - Gabriel
>
> On Tuesday, November 11, 2003, at 05:57 PM, Michael Shuler wrote:
>
> > Thanks for the quick help everyone...OK I made a few changes....
> >
> > The Query is now:
> > SELECT COUNT( * ) AS CallCount FROM ServiceRADIUSAccounting WHERE
> > (Realm =
> > 'testrealm.com') AND ('2003-10-11 16:00:00' BETWEEN AcctStartTime
AND
> > AcctStopTime)
> > Which uses the RealmAndStart index (which as you see in the next
line
> > has
> > been improved slightly).
> >
> > And I modified the last key to:
> > KEY `RealmAndStart` (`Realm`,`AcctStartTime`,`AcctStopTime`)
> >
> > I also though about it for a while and had an inspirational idea
that
> > if I
> > make a key that looks like this:
> > KEY `StartStopRealm` (`AcctStartTime`,`AcctStopTime`,`Realm`)
> >
> > It would go even faster because it will narrow down to the records
> > within
> > the time frame (which is about 1000 records) and then down by the
> > realm name
> > which would result in ~150 records to count. Oddly enough in the
> > EXPLAIN it
> > doesn't even consider it as a possible index to use. What gets even
> > more
> > odd is that I swapped the BETWEEN and the Realm in the WHERE clause
> > and then
> > it decided to use just the plain Realm index...
> >
> > SELECT COUNT( * ) AS CallCount FROM ServiceRADIUSAccounting WHERE
> > ('2003-10-11 16:00:00' BETWEEN AcctStartTime AND AcctStopTime) AND
> > (Realm =
> > 'testrealm.com')
> >
> >
> > Is this a bug and is there a way to force MySQL to use an index that
> > you
> > know is a better choice?
> >
> > Thanks again,
> > Michael Shuler
> >
> >
> > -----Original Message-----
> > From: Michael Shuler [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, November 11, 2003 3:44 PM
> > To: [EMAIL PROTECTED]
> > Subject: Dirt Slow Query On Datetime Range
> >
> >
> > 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]
