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 !
4  3471 
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 !
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
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
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 ! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Joseph Norris |
last post by:
Group,
I have been working with Mysql for about 5 years - mainly in LAMP shops. The
tables have been between 20-100 thousand records size. Now I have a project
where the tables are in the...
|
by: Matt Liverance |
last post by:
I REALLY dont want to switch to oracle :( but I cant get these tables
working any faster.
I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm
raid 5 on u320 perc raid...
|
by: gizmo |
last post by:
I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow...
|
by: DJJ |
last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time...
|
by: Ryan |
last post by:
I came across a situation that I've been unable to explain and was
hoping somebody had an answer:
I had written an update query which was taking about 8 seconds to run
and considered it too...
|
by: DFS |
last post by:
This UNION query is very slow. With only 3,000 records in the Parent table
and 7,000 records in the Child table, it takes about 60 seconds to run and
returns about 2200 records.
Access 97. All...
|
by: Coy Howe |
last post by:
This one seems bizarre!
We have a database consisting of a main table and 12 - 15 "sub" tables,
which are connected via cascading relationships. The database performs
many complex calculations...
|
by: AnandaSim |
last post by:
Hi All,
I've had Access 97, 2000 connections to the corporate Oracle database
for a few years now - but seldom use it. When I did use it years ago,
performance was not fast but the features were...
|
by: John Rivers |
last post by:
Hello
has anybody else noticed
I have queries that SQL 2000 optimizes correctly and they run very
fast
Yet SQL 2005 keeps using a dumb query plan and queries run very slow
The problem...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
