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_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`,`PollValue`),
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,'100','2006-0 3-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,'100','2006-0 3-07
00:40:01','657315363','2006-03-07
00:35:00',309,11644,301,'657303719'),(2915758,0,'99','2006-0 3-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,'100','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,'99','2006-0 3-07
01:05:00','238866321','2006-03-07 01:00:01',91,3384,299,'238862937');


Thanks in advance for your help !

Re: Optimizing Slow Queries on millions of records

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`,`PollValue`),
> 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,'100','2006-0 3-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,'100','2006-0 3-07
> 00:40:01','657315363','2006-03-07
> 00:35:00',309,11644,301,'657303719'),(2915758,0,'99','2006-0 3-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,'100','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,'99','2006-0 3-07
> 01:05:00','238866321','2006-03-07 01:00:01',91,3384,299,'238862937');
>
>
> Thanks in advance for your help !

Re: Optimizing Slow Queries on millions of records

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

Re: Optimizing Slow Queries on millions of records

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" wrote in message
news:fe7937f1bb9a6d92c957171f5fb77bca$1@www.firstdbasource.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_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
>
>

Re: Optimizing Slow Queries on millions of records

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" wrote in message
news:1141725459.925903.56520@i39g2000cwa.googlegroups.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`,`PollValue`),
>> 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,'100','2006-0 3-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,'100','2006-0 3-07
>> 00:40:01','657315363','2006-03-07
>> 00:35:00',309,11644,301,'657303719'),(2915758,0,'99','2006-0 3-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,'100','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,'99','2006-0 3-07
>> 01:05:00','238866321','2006-03-07 01:00:01',91,3384,299,'238862937');
>>
>>
>> Thanks in advance for your help !
>