473,847 Members | 2,462 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Optimizing Slow Queries on millions of records

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_Po llData Where
OIDID=99 Group By Month(PollTime) , Year(PollTime);

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

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

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

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

6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And PollTime<>PrevP ollTime;

9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And PollTime<>PrevP ollTime;

*************** *************** ********
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`,`PollT ime`,`PrevPollT ime`,`PollDelta `,`PollValue`),
KEY `PollData_IDX2` (`OIDID`,`PollT ime`,`PrevPollT ime`)
) 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','2388 38367','0000-00-00
00:00:00',0,0,0 ,'0'),(2915549, 0,'100','2006-03-07
00:25:01','6572 85874','0000-00-00
00:00:00',0,0,0 ,'0'),(2915567, 0,'99','2006-03-07
00:30:00','2388 41751','2006-03-07
00:25:01',91,33 84,299,'2388383 67'),(2915604,0 ,'100','2006-03-07
00:30:00','6572 95674','2006-03-07
00:25:01',262,9 800,299,'657285 874'),(2915629, 0,'100','2006-03-07
00:35:00','6573 03719','2006-03-07
00:30:00',215,8 045,300,'657295 674'),(2915649, 0,'99','2006-03-07
00:35:00','2388 45071','2006-03-07
00:30:00',89,33 20,300,'2388417 51'),(2915688,0 ,'99','2006-03-07
00:40:00','2388 49529','2006-03-07
00:35:00',119,4 458,300,'238845 071'),(2915703, 0,'100','2006-03-07
00:40:01','6573 15363','2006-03-07
00:35:00',309,1 1644,301,'65730 3719'),(2915758 ,0,'99','2006-03-07
00:45:00','2388 52913','2006-03-07
00:40:00',90,33 84,300,'2388495 29'),(2915772,0 ,'100','2006-03-07
00:45:01','6573 24240','2006-03-07
00:40:01',237,8 877,300,'657315 363'),(2915785, 0,'99','2006-03-07
00:50:00','2388 56233','2006-03-07
00:45:00',89,33 20,300,'2388529 13'),(2915824,0 ,'100','2006-03-07
00:50:01','6573 37533','2006-03-07
00:45:01',354,1 3293,300,'65732 4240'),(2915854 ,0,'100','2006-03-07
00:55:00','6573 47205','2006-03-07
00:50:01',259,9 672,299,'657337 533'),(2915863, 0,'99','2006-03-07
00:55:00','2388 59617','2006-03-07
00:50:00',90,33 84,300,'2388562 33'),(2915918,0 ,'100','2006-03-07
01:00:00','6573 54866','2006-03-07
00:55:00',204,7 661,300,'657347 205'),(2915950, 0,'99','2006-03-07
01:00:01','2388 62937','2006-03-07
00:55:00',88,33 20,301,'2388596 17'),(2915965,0 ,'100','2006-03-07
01:05:00','6573 68415','2006-03-07
01:00:00',361,1 3549,300,'65735 4866'),(2915991 ,0,'99','2006-03-07
01:05:00','2388 66321','2006-03-07 01:00:01',91,33 84,299,'2388629 37');
Thanks in advance for your help !
Mar 7 '06 #1
4 3499
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_Po llData Where
OIDID=99 Group By Month(PollTime) , Year(PollTime);

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

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

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

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

6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And PollTime<>PrevP ollTime;

9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And PollTime<>PrevP ollTime;

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(PollTim e)=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`,`PollT ime`,`PrevPollT ime`,`PollDelta `,`PollValue`),
KEY `PollData_IDX2` (`OIDID`,`PollT ime`,`PrevPollT ime`)
) 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','2388 38367','0000-00-00
00:00:00',0,0,0 ,'0'),(2915549, 0,'100','2006-03-07
00:25:01','6572 85874','0000-00-00
00:00:00',0,0,0 ,'0'),(2915567, 0,'99','2006-03-07
00:30:00','2388 41751','2006-03-07
00:25:01',91,33 84,299,'2388383 67'),(2915604,0 ,'100','2006-03-07
00:30:00','6572 95674','2006-03-07
00:25:01',262,9 800,299,'657285 874'),(2915629, 0,'100','2006-03-07
00:35:00','6573 03719','2006-03-07
00:30:00',215,8 045,300,'657295 674'),(2915649, 0,'99','2006-03-07
00:35:00','2388 45071','2006-03-07
00:30:00',89,33 20,300,'2388417 51'),(2915688,0 ,'99','2006-03-07
00:40:00','2388 49529','2006-03-07
00:35:00',119,4 458,300,'238845 071'),(2915703, 0,'100','2006-03-07
00:40:01','6573 15363','2006-03-07
00:35:00',309,1 1644,301,'65730 3719'),(2915758 ,0,'99','2006-03-07
00:45:00','2388 52913','2006-03-07
00:40:00',90,33 84,300,'2388495 29'),(2915772,0 ,'100','2006-03-07
00:45:01','6573 24240','2006-03-07
00:40:01',237,8 877,300,'657315 363'),(2915785, 0,'99','2006-03-07
00:50:00','2388 56233','2006-03-07
00:45:00',89,33 20,300,'2388529 13'),(2915824,0 ,'100','2006-03-07
00:50:01','6573 37533','2006-03-07
00:45:01',354,1 3293,300,'65732 4240'),(2915854 ,0,'100','2006-03-07
00:55:00','6573 47205','2006-03-07
00:50:01',259,9 672,299,'657337 533'),(2915863, 0,'99','2006-03-07
00:55:00','2388 59617','2006-03-07
00:50:00',90,33 84,300,'2388562 33'),(2915918,0 ,'100','2006-03-07
01:00:00','6573 54866','2006-03-07
00:55:00',204,7 661,300,'657347 205'),(2915950, 0,'99','2006-03-07
01:00:01','2388 62937','2006-03-07
00:55:00',88,33 20,301,'2388596 17'),(2915965,0 ,'100','2006-03-07
01:05:00','6573 68415','2006-03-07
01:00:00',361,1 3549,300,'65735 4866'),(2915991 ,0,'99','2006-03-07
01:05:00','2388 66321','2006-03-07 01:00:01',91,33 84,299,'2388629 37');
Thanks in advance for your help !


Mar 7 '06 #2
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_Po llData Where
OIDID=99 Group By Month(PollTime) , Year(PollTime); 2- SELECT PollRate As 95th FROM db_snmp3.tbl_Po llData Where (OIDID=99) and
((Month(PollTim e)=3 and Year(PollTime)= 2006) Or (Year(PollTime) =2006 and
Month(PollTime) =4 and Hour(PollTime)= '00' and Minute(PollTime )='00')) And
PollTime<>PrevP ollTime Order by PollRate Desc Limit 87.69,1; 3- SELECT PollRate As 95th FROM db_snmp3.tbl_Po llData Where (OIDID=100) and
((Month(PollTim e)=3 and Year(PollTime)= 2006) Or (Year(PollTime) =2006 and
Month(PollTime) =4 and Hour(PollTime)= '00' and Minute(PollTime )='00')) And
PollTime<>PrevP ollTime Order by PollRate Desc Limit 87.69,1; 4- SELECT PollRate As 95th FROM db_snmp3.tbl_Po llData Where (OIDID=99) and
((Month(PollTim e)=3 and Year(PollTime)= 2006) Or (Year(PollTime) =2006 and
Month(PollTime) =4 and Hour(PollTime)= '00' and Minute(PollTime )='00')) And
PollTime<>PrevP ollTime Order by PollRate Desc Limit 447,1; 5- SELECT PollRate As 95th FROM db_snmp3.tbl_Po llData Where (OIDID=100) and
((Month(PollTim e)=3 and Year(PollTime)= 2006) Or (Year(PollTime) =2006 and
Month(PollTime) =4 and Hour(PollTime)= '00' and Minute(PollTime )='00')) And
PollTime<>PrevP ollTime Order by PollRate Desc Limit 447,1; 6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And PollTime<>PrevP ollTime; 9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And PollTime<>PrevP ollTime;
[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
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.f irstdbasource.c om...
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_Po llData
Where
OIDID=99 Group By Month(PollTime) , Year(PollTime);

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

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

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

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

6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And
PollTime<>PrevP ollTime;

9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And
PollTime<>PrevP ollTime;


[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
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******** *************@i 39g2000cwa.goog legroups.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_Po llData
Where
OIDID=99 Group By Month(PollTime) , Year(PollTime);

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

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

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

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

6- SELECT Max(PollRate) As MaxIn FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=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_Po llData Where
PollRate<100000 000 And (OIDID=99) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And
PollTime<>PrevP ollTime;

9- SELECT Sum(PollDelta) As TotalOut FROM db_snmp3.tbl_Po llData Where
PollRate<100000 000 And (OIDID=100) and ((Month(PollTim e)=3 and
Year(PollTime)= 2006) Or (Year(PollTime) =2006 and Month(PollTime) =4 and
Hour(PollTime)= '00' and Minute(PollTime )='00')) And
PollTime<>PrevP ollTime;


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(PollTim e)=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`,`PollT ime`,`PrevPollT ime`,`PollDelta `,`PollValue`),
KEY `PollData_IDX2` (`OIDID`,`PollT ime`,`PrevPollT ime`)
) 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','2388 38367','0000-00-00
00:00:00',0,0,0 ,'0'),(2915549, 0,'100','2006-03-07
00:25:01','6572 85874','0000-00-00
00:00:00',0,0,0 ,'0'),(2915567, 0,'99','2006-03-07
00:30:00','2388 41751','2006-03-07
00:25:01',91,33 84,299,'2388383 67'),(2915604,0 ,'100','2006-03-07
00:30:00','6572 95674','2006-03-07
00:25:01',262,9 800,299,'657285 874'),(2915629, 0,'100','2006-03-07
00:35:00','6573 03719','2006-03-07
00:30:00',215,8 045,300,'657295 674'),(2915649, 0,'99','2006-03-07
00:35:00','2388 45071','2006-03-07
00:30:00',89,33 20,300,'2388417 51'),(2915688,0 ,'99','2006-03-07
00:40:00','2388 49529','2006-03-07
00:35:00',119,4 458,300,'238845 071'),(2915703, 0,'100','2006-03-07
00:40:01','6573 15363','2006-03-07
00:35:00',309,1 1644,301,'65730 3719'),(2915758 ,0,'99','2006-03-07
00:45:00','2388 52913','2006-03-07
00:40:00',90,33 84,300,'2388495 29'),(2915772,0 ,'100','2006-03-07
00:45:01','6573 24240','2006-03-07
00:40:01',237,8 877,300,'657315 363'),(2915785, 0,'99','2006-03-07
00:50:00','2388 56233','2006-03-07
00:45:00',89,33 20,300,'2388529 13'),(2915824,0 ,'100','2006-03-07
00:50:01','6573 37533','2006-03-07
00:45:01',354,1 3293,300,'65732 4240'),(2915854 ,0,'100','2006-03-07
00:55:00','6573 47205','2006-03-07
00:50:01',259,9 672,299,'657337 533'),(2915863, 0,'99','2006-03-07
00:55:00','2388 59617','2006-03-07
00:50:00',90,33 84,300,'2388562 33'),(2915918,0 ,'100','2006-03-07
01:00:00','6573 54866','2006-03-07
00:55:00',204,7 661,300,'657347 205'),(2915950, 0,'99','2006-03-07
01:00:01','2388 62937','2006-03-07
00:55:00',88,33 20,301,'2388596 17'),(2915965,0 ,'100','2006-03-07
01:05:00','6573 68415','2006-03-07
01:00:00',361,1 3549,300,'65735 4866'),(2915991 ,0,'99','2006-03-07
01:05:00','2388 66321','2006-03-07 01:00:01',91,33 84,299,'2388629 37');
Thanks in advance for your help !

Mar 8 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1780
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 millions of records. This is very new to me and I am noticing that my queries are really slowwwwww! What are the options that I have to speed my queries on the mysql side with
6
22543
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 cards, dell 2600/4600's with single channel backplanes (new ones will have dual channel) All have 2 gig of ram, but I've never seen mysql use more than 300mb of ram.
3
2791
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 for my requirements. Is there any way I can optimize this query. I have thought about using an indexed view. I haven't done one before, does anyone know if this would have potential to improve performance or indeed any other performance...
11
17588
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 to load making any kind linkage with my Access data virtually useless. I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting out on a server and the Access database is running locally. The network connection is very...
6
4573
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 slow. I copied the SQL statement from the query and tried executing it from code which then ran in 1 second. To make sure that I didn't miss anything, I copied the SQL statement back into a query and tried running it again. It now also only took 1...
7
2598
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 appropriate indexes and keys enforced. Any ideas on speeding it up? Thanks. -- PART 1: HAS NO CHILD RECORDS
1
1893
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 and displays the results in both forms and reports. In the process of developing the database we "entered" 12 sample clients. Before packaging the database we remove the sample clients by deleting them from the main table, which cascades the...
9
3012
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 good enough. Now, the version of Access is 2003 with Office SP1 applied, Windows XP against Oracle 9.2. And attached tables are real slow - 5 mins to get to datasheet view and data is incorrect - the same row is repeated several times even in...
5
2882
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 seems to stem from the assumption that data in a derived
0
9727
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10978
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10643
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10705
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10330
tracyyun
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9477
agi2029
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5907
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4521
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 we have to send another system
3
3158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.