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
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
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
"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
> > And here is the query:
> >
> >
, 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
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Change your query to use BETWEEN rather that <= and >=.
>
> --ja
> > And here is the query:
> >
> > SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting
> WHERE (Realm =
> > 'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00'
Change your query to use BETWEEN rather that <= and >=.
--ja
On Tue, 11 Nov 2003, 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
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, ha
