By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,756 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,756 IT Pros & Developers. It's quick & easy.

Optimizing Slow Queries on millions of records

P: n/a
Hello Group,

I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and then
displays the results on the webpage.

When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
Now it takes about 38 seconds for all queries in the example below.
MySQL 4.1 on Windows

I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time.

Any help in optimizing this to minimize the query times is greatly
appreciated.

**************************************

Here are the queries that are run sequentially:
1- Select Month(PollTime), Year(PollTime) From db_snmp3.tbl_PollData Where
OIDID=99 Group By Month(PollTime), Year(PollTime);

2- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1;

3- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1;

4- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1;

5- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1;

6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00'));

7- SELECT Max(PollRate) As MaxOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00'));

8- SELECT Sum(PollDelta) As TotalIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And PollTime<>PrevPollTime;

9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And PollTime<>PrevPollTime;

**************************************
Here is the DB structure:

# Host: localhost
# Database: db_snmp3
# Table: 'tbl_polldata'
#
CREATE TABLE `tbl_polldata` (
`PollID` int(11) NOT NULL auto_increment,
`HostID` int(11) NOT NULL default '0',
`OIDID` varchar(100) NOT NULL default '',
`PollTime` datetime NOT NULL default '0000-00-00 00:00:00',
`PollValue` varchar(100) NOT NULL default '',
`PrevPollTime` datetime NOT NULL default '0000-00-00 00:00:00',
`PollRate` int(11) NOT NULL default '0',
`PollDelta` int(11) NOT NULL default '0',
`TimeDelta` int(11) NOT NULL default '0',
`PrevPollValue` varchar(100) NOT NULL default '',
PRIMARY KEY (`PollID`),
KEY `PollData_IDX`
(`OIDID`,`PollTime`,`PrevPollTime`,`PollDelta`,`Po llValue`),
KEY `PollData_IDX2` (`OIDID`,`PollTime`,`PrevPollTime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

**************************************
And here is some sample data:
INSERT INTO `tbl_polldata` VALUES (2915528,0,'99','2006-03-07
00:25:01','238838367','0000-00-00
00:00:00',0,0,0,'0'),(2915549,0,'100','2006-03-07
00:25:01','657285874','0000-00-00
00:00:00',0,0,0,'0'),(2915567,0,'99','2006-03-07
00:30:00','238841751','2006-03-07
00:25:01',91,3384,299,'238838367'),(2915604,0,'100 ','2006-03-07
00:30:00','657295674','2006-03-07
00:25:01',262,9800,299,'657285874'),(2915629,0,'10 0','2006-03-07
00:35:00','657303719','2006-03-07
00:30:00',215,8045,300,'657295674'),(2915649,0,'99 ','2006-03-07
00:35:00','238845071','2006-03-07
00:30:00',89,3320,300,'238841751'),(2915688,0,'99' ,'2006-03-07
00:40:00','238849529','2006-03-07
00:35:00',119,4458,300,'238845071'),(2915703,0,'10 0','2006-03-07
00:40:01','657315363','2006-03-07
00:35:00',309,11644,301,'657303719'),(2915758,0,'9 9','2006-03-07
00:45:00','238852913','2006-03-07
00:40:00',90,3384,300,'238849529'),(2915772,0,'100 ','2006-03-07
00:45:01','657324240','2006-03-07
00:40:01',237,8877,300,'657315363'),(2915785,0,'99 ','2006-03-07
00:50:00','238856233','2006-03-07
00:45:00',89,3320,300,'238852913'),(2915824,0,'100 ','2006-03-07
00:50:01','657337533','2006-03-07
00:45:01',354,13293,300,'657324240'),(2915854,0,'1 00','2006-03-07
00:55:00','657347205','2006-03-07
00:50:01',259,9672,299,'657337533'),(2915863,0,'99 ','2006-03-07
00:55:00','238859617','2006-03-07
00:50:00',90,3384,300,'238856233'),(2915918,0,'100 ','2006-03-07
01:00:00','657354866','2006-03-07
00:55:00',204,7661,300,'657347205'),(2915950,0,'99 ','2006-03-07
01:00:01','238862937','2006-03-07
00:55:00',88,3320,301,'238859617'),(2915965,0,'100 ','2006-03-07
01:05:00','657368415','2006-03-07
01:00:00',361,13549,300,'657354866'),(2915991,0,'9 9','2006-03-07
01:05:00','238866321','2006-03-07 01:00:01',91,3384,299,'238862937');
Thanks in advance for your help !
Mar 7 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Got2Go wrote:
Hello Group,

I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and then
displays the results on the webpage.

When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
Now it takes about 38 seconds for all queries in the example below.
MySQL 4.1 on Windows

I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time.

Any help in optimizing this to minimize the query times is greatly
appreciated.

**************************************

Here are the queries that are run sequentially:
1- Select Month(PollTime), Year(PollTime) From db_snmp3.tbl_PollData Where
OIDID=99 Group By Month(PollTime), Year(PollTime);

2- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1;

3- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1;

4- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1;

5- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1;

6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00'));

7- SELECT Max(PollRate) As MaxOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00'));

8- SELECT Sum(PollDelta) As TotalIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And PollTime<>PrevPollTime;

9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And PollTime<>PrevPollTime;

Have you tried "EXPLAIN" on your queries, I doubt if you've applied
indexes on your queries by using Month(), Year()...... functions of
your indexed attributes in some comparison expressions of WHERE
clauses..

change somthing like this:
((Month(PollTime)=3 and Year(PollTime)=2006)
to:
(PollTime <= '2006-03-31' and PollTime >= '2006-03-01')
may help improve your queries..

Best,
Xicheng
**************************************
Here is the DB structure:

# Host: localhost
# Database: db_snmp3
# Table: 'tbl_polldata'
#
CREATE TABLE `tbl_polldata` (
`PollID` int(11) NOT NULL auto_increment,
`HostID` int(11) NOT NULL default '0',
`OIDID` varchar(100) NOT NULL default '',
`PollTime` datetime NOT NULL default '0000-00-00 00:00:00',
`PollValue` varchar(100) NOT NULL default '',
`PrevPollTime` datetime NOT NULL default '0000-00-00 00:00:00',
`PollRate` int(11) NOT NULL default '0',
`PollDelta` int(11) NOT NULL default '0',
`TimeDelta` int(11) NOT NULL default '0',
`PrevPollValue` varchar(100) NOT NULL default '',
PRIMARY KEY (`PollID`),
KEY `PollData_IDX`
(`OIDID`,`PollTime`,`PrevPollTime`,`PollDelta`,`Po llValue`),
KEY `PollData_IDX2` (`OIDID`,`PollTime`,`PrevPollTime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

**************************************
And here is some sample data:
INSERT INTO `tbl_polldata` VALUES (2915528,0,'99','2006-03-07
00:25:01','238838367','0000-00-00
00:00:00',0,0,0,'0'),(2915549,0,'100','2006-03-07
00:25:01','657285874','0000-00-00
00:00:00',0,0,0,'0'),(2915567,0,'99','2006-03-07
00:30:00','238841751','2006-03-07
00:25:01',91,3384,299,'238838367'),(2915604,0,'100 ','2006-03-07
00:30:00','657295674','2006-03-07
00:25:01',262,9800,299,'657285874'),(2915629,0,'10 0','2006-03-07
00:35:00','657303719','2006-03-07
00:30:00',215,8045,300,'657295674'),(2915649,0,'99 ','2006-03-07
00:35:00','238845071','2006-03-07
00:30:00',89,3320,300,'238841751'),(2915688,0,'99' ,'2006-03-07
00:40:00','238849529','2006-03-07
00:35:00',119,4458,300,'238845071'),(2915703,0,'10 0','2006-03-07
00:40:01','657315363','2006-03-07
00:35:00',309,11644,301,'657303719'),(2915758,0,'9 9','2006-03-07
00:45:00','238852913','2006-03-07
00:40:00',90,3384,300,'238849529'),(2915772,0,'100 ','2006-03-07
00:45:01','657324240','2006-03-07
00:40:01',237,8877,300,'657315363'),(2915785,0,'99 ','2006-03-07
00:50:00','238856233','2006-03-07
00:45:00',89,3320,300,'238852913'),(2915824,0,'100 ','2006-03-07
00:50:01','657337533','2006-03-07
00:45:01',354,13293,300,'657324240'),(2915854,0,'1 00','2006-03-07
00:55:00','657347205','2006-03-07
00:50:01',259,9672,299,'657337533'),(2915863,0,'99 ','2006-03-07
00:55:00','238859617','2006-03-07
00:50:00',90,3384,300,'238856233'),(2915918,0,'100 ','2006-03-07
01:00:00','657354866','2006-03-07
00:55:00',204,7661,300,'657347205'),(2915950,0,'99 ','2006-03-07
01:00:01','238862937','2006-03-07
00:55:00',88,3320,301,'238859617'),(2915965,0,'100 ','2006-03-07
01:05:00','657368415','2006-03-07
01:00:00',361,13549,300,'657354866'),(2915991,0,'9 9','2006-03-07
01:05:00','238866321','2006-03-07 01:00:01',91,3384,299,'238862937');
Thanks in advance for your help !


Mar 7 '06 #2

P: n/a
Got2Go wrote:

[snip]
I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time. Any help in optimizing this to minimize the query times is greatly
appreciated. ************************************** Here are the queries that are run sequentially:
1- Select Month(PollTime), Year(PollTime) From db_snmp3.tbl_PollData Where
OIDID=99 Group By Month(PollTime), Year(PollTime); 2- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1; 3- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1; 4- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1; 5- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100) and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1; 6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')); 7- SELECT Max(PollRate) As MaxOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')); 8- SELECT Sum(PollDelta) As TotalIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And PollTime<>PrevPollTime; 9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And PollTime<>PrevPollTime;
[snip]
Executing functions is extremely expensive - especially if you have
"millions" of records - and you want your where clause to be simple (no
functions unless absolutely necessary.) And in your case, they are not
necessary.

where odid='99' and Polltime between '02-14-2006 00:00' and '02-21-2006
00:00'
(use appropriate date format)

You can also try re-arranging the order of your index - creating a second
index

polltime,odid
Thanks in advance for your help !


np
Mar 7 '06 #3

P: n/a
Hi noone,

I tried your suggestions.
Added another index pollid,OIDID.
And changed the queries to use between 'datetime' and 'datetime'.

This did improve the speed from about 38 seconds to about 20 for the same
set of queries.

Can you think of anything else that can be done here ?
Is there anything else I can post here that will help in determining what
can be improved ?

Thanks again!
"noone" <no***@nowhere.com> wrote in message
news:fe********************************@www.firstd basource.com...
Got2Go wrote:

[snip]
I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time.

Any help in optimizing this to minimize the query times is greatly
appreciated.

**************************************

Here are the queries that are run sequentially:
1- Select Month(PollTime), Year(PollTime) From db_snmp3.tbl_PollData
Where
OIDID=99 Group By Month(PollTime), Year(PollTime);

2- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99)
and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1;

3- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100)
and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1;

4- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99)
and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1;

5- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100)
and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1;

6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00'));

7- SELECT Max(PollRate) As MaxOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00'));

8- SELECT Sum(PollDelta) As TotalIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime;

9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime;


[snip]
Executing functions is extremely expensive - especially if you have
"millions" of records - and you want your where clause to be simple (no
functions unless absolutely necessary.) And in your case, they are not
necessary.

where odid='99' and Polltime between '02-14-2006 00:00' and '02-21-2006
00:00'
(use appropriate date format)

You can also try re-arranging the order of your index - creating a second
index
polltime,odid
Thanks in advance for your help !


np

Mar 8 '06 #4

P: n/a
Hi Xicheng,

I tried a ferw things as suggested by noone on this same thread.
Please see my reply to him.
I guess it is similar to one of your suggestions about the datetime in the
where clause.

Can you see anything else that can be done to improve performance of the
queries ?
Is it wrong to assume these queries/db could be improved be done in less
than a second each ?

Thanks for your help!

"Xicheng" <xi*****@gmail.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...
Got2Go wrote:
Hello Group,

I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and
then
displays the results on the webpage.

When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
Now it takes about 38 seconds for all queries in the example below.
MySQL 4.1 on Windows

I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time.

Any help in optimizing this to minimize the query times is greatly
appreciated.

**************************************

Here are the queries that are run sequentially:
1- Select Month(PollTime), Year(PollTime) From db_snmp3.tbl_PollData
Where
OIDID=99 Group By Month(PollTime), Year(PollTime);

2- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99)
and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1;

3- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100)
and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 87.69,1;

4- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=99)
and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1;

5- SELECT PollRate As 95th FROM db_snmp3.tbl_PollData Where (OIDID=100)
and
((Month(PollTime)=3 and Year(PollTime)=2006) Or (Year(PollTime)=2006 and
Month(PollTime)=4 and Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime Order by PollRate Desc Limit 447,1;

6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00'));

7- SELECT Max(PollRate) As MaxOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00'));

8- SELECT Sum(PollDelta) As TotalIn FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=99) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime;

9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_PollData Where
PollRate<100000000 And (OIDID=100) and ((Month(PollTime)=3 and
Year(PollTime)=2006) Or (Year(PollTime)=2006 and Month(PollTime)=4 and
Hour(PollTime)='00' and Minute(PollTime)='00')) And
PollTime<>PrevPollTime;


Have you tried "EXPLAIN" on your queries, I doubt if you've applied
indexes on your queries by using Month(), Year()...... functions of
your indexed attributes in some comparison expressions of WHERE
clauses..

change somthing like this:
((Month(PollTime)=3 and Year(PollTime)=2006)
to:
(PollTime <= '2006-03-31' and PollTime >= '2006-03-01')
may help improve your queries..

Best,
Xicheng
**************************************
Here is the DB structure:

# Host: localhost
# Database: db_snmp3
# Table: 'tbl_polldata'
#
CREATE TABLE `tbl_polldata` (
`PollID` int(11) NOT NULL auto_increment,
`HostID` int(11) NOT NULL default '0',
`OIDID` varchar(100) NOT NULL default '',
`PollTime` datetime NOT NULL default '0000-00-00 00:00:00',
`PollValue` varchar(100) NOT NULL default '',
`PrevPollTime` datetime NOT NULL default '0000-00-00 00:00:00',
`PollRate` int(11) NOT NULL default '0',
`PollDelta` int(11) NOT NULL default '0',
`TimeDelta` int(11) NOT NULL default '0',
`PrevPollValue` varchar(100) NOT NULL default '',
PRIMARY KEY (`PollID`),
KEY `PollData_IDX`
(`OIDID`,`PollTime`,`PrevPollTime`,`PollDelta`,`Po llValue`),
KEY `PollData_IDX2` (`OIDID`,`PollTime`,`PrevPollTime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

**************************************
And here is some sample data:
INSERT INTO `tbl_polldata` VALUES (2915528,0,'99','2006-03-07
00:25:01','238838367','0000-00-00
00:00:00',0,0,0,'0'),(2915549,0,'100','2006-03-07
00:25:01','657285874','0000-00-00
00:00:00',0,0,0,'0'),(2915567,0,'99','2006-03-07
00:30:00','238841751','2006-03-07
00:25:01',91,3384,299,'238838367'),(2915604,0,'100 ','2006-03-07
00:30:00','657295674','2006-03-07
00:25:01',262,9800,299,'657285874'),(2915629,0,'10 0','2006-03-07
00:35:00','657303719','2006-03-07
00:30:00',215,8045,300,'657295674'),(2915649,0,'99 ','2006-03-07
00:35:00','238845071','2006-03-07
00:30:00',89,3320,300,'238841751'),(2915688,0,'99' ,'2006-03-07
00:40:00','238849529','2006-03-07
00:35:00',119,4458,300,'238845071'),(2915703,0,'10 0','2006-03-07
00:40:01','657315363','2006-03-07
00:35:00',309,11644,301,'657303719'),(2915758,0,'9 9','2006-03-07
00:45:00','238852913','2006-03-07
00:40:00',90,3384,300,'238849529'),(2915772,0,'100 ','2006-03-07
00:45:01','657324240','2006-03-07
00:40:01',237,8877,300,'657315363'),(2915785,0,'99 ','2006-03-07
00:50:00','238856233','2006-03-07
00:45:00',89,3320,300,'238852913'),(2915824,0,'100 ','2006-03-07
00:50:01','657337533','2006-03-07
00:45:01',354,13293,300,'657324240'),(2915854,0,'1 00','2006-03-07
00:55:00','657347205','2006-03-07
00:50:01',259,9672,299,'657337533'),(2915863,0,'99 ','2006-03-07
00:55:00','238859617','2006-03-07
00:50:00',90,3384,300,'238856233'),(2915918,0,'100 ','2006-03-07
01:00:00','657354866','2006-03-07
00:55:00',204,7661,300,'657347205'),(2915950,0,'99 ','2006-03-07
01:00:01','238862937','2006-03-07
00:55:00',88,3320,301,'238859617'),(2915965,0,'100 ','2006-03-07
01:05:00','657368415','2006-03-07
01:00:00',361,13549,300,'657354866'),(2915991,0,'9 9','2006-03-07
01:05:00','238866321','2006-03-07 01:00:01',91,3384,299,'238862937');
Thanks in advance for your help !

Mar 8 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.