Hi,
Well, as a side note, you *could* use
'2003-11-11 15:30:00' BETWEEN AcctStopTime AND AcctStartTime
but MySQL can't optimize that to use indexes.
Besides, even if it was on the same column, BETWEEN is treated exactly
the same as <= and >= so BETWEEN wouldn't help anyway (except for
legibility). :-)
Michael, I wouldn't worry about changing VARCHAR to CHAR. Instead, add
AcctStopTime to the end of the "RealmAndStart" index:
ALTER TABLE ServiceRADIUSAccounting
DROP INDEX Realm, -- This is redundant anyway
DROP INDEX RealmAndStart,
ADD INDEX RealmAndStartAndStop (Realm, AcctStartTime, AcctStopTime);
Then it won't need the random seeks to the data file (EXPLAIN should say
"Using index"). Might make it fast enough that you can go back to
MyISAM. :-)
Hope that helps.
Matt
----- Original Message -----
From: "Mike Johnson"
Sent: Tuesday, November 11, 2003 4:17 PM
Subject: RE: Dirt Slow Query On Datetime Range
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Change your query to use BETWEEN rather that <= and >=.
>
> --ja
<snip>
> > 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')
His WHERE clause is on two different fields (AcctStartTime and
AcctStopTime). I don't think a BETWEEN clause is what's needed...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
