Re: Dirt Slow Query On Datetime Range...the saga continues
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 '-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '-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
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 '-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '-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:
tabletype 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
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 '-00-00 00:00:00', `AcctStopTime` datetime NOT NULL default '-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: tabletype 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]
Re: Dirt Slow Query On Datetime Range
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 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 '-00-00 00:00:00', `AcctStopTime` datetime NOT NULL default '-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: tabletype 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] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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... -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dirt Slow Query On Datetime Range
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 '-00-00 00:00:00', `AcctStopTime` datetime NOT NULL default '-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: tabletype 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]
RE: Dirt Slow Query On Datetime Range...the saga continues
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 '-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '-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:
tabletype 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
