php mysql优化

事实：
– 有3个数据库在使用中. dbCurrent,dbStats,dbBackup.
dbCurrent是网站的主要数据库
dbStats包含各种统计表和跟踪数据表
dbBackup保存最近五年的统计/跟踪表.

>我将使用的数据来自两个数据库(dbStats,dbBackup)
>表名是：stats2006,stats2007,stats2008等,除了当前的统计数据只是“统计数据”.每个表都有其年份的数据.
>每年数据的表结构是相同的：
primaryID字段是整数
productID字段是整数
dateMonitor字段是整数(unixtimestamp)
pageName字段是varchar(20)
> productID,dateMonitor,pageName字段也有索引

换句话说,在什么日期和哪个页面查看了什么产品.

所以,我认为是从每个表创建一个循环并获取我的数据.
我的每个查询看起来像：

Select COUNT(primaryID) as myCounter FROM $tablename WHERE $conditions

其中$tablename和$conditions是基于每个循环的变量.
所有条件类似于：

> date1和date2之间的dateMonitor
> pageName =’some val’
> productID IN($comma_separated_values)
>以上的组合

到目前为止,所有这些都正常运作(对于单一产品).

当我尝试创建一个报告来比较’y’年的’x’产品(从admin / moderator动态选择)时,脚本运行时间超过15分钟.

我正在寻找一种方法来提高脚本的性能.
到目前为止我使用的逻辑/结构如下：

Loop through products to find the ids to use (typical format is: x,y,z (comma separated values)
Open Loop through years/months
Execute one sql query for each affected table/database to get the number of affected rows.
Close year loop
Send data to graph script (jquery jqPlot to be exact) to print on screen

任何帮助/想法赞赏.

编辑：
基于@Narf与UNION ALL的建议,我基于12个子选择语句构建了1个单一查询：

每个select语句指的是一个月的持续时间.演示代码：


EDIT2：创建组合索引后(如@ypercube所示),我看到执行时间略有减少.
现在我的平均执行时间为11分钟(原始时间为15-17分钟)
这有助于减少执行时间.
谢谢.
SELECT COUNT(*) AS monthlyTotal FROM db1.table1 WHERE dateMonitor>='11676
Loop through products to find the ids to use (typical format is: x,z (comma separated values)
Open Loop through years/months
Execute one sql query for each affected table/database to get the number of affected rows.
Close year loop
Send data to graph script (jquery jqPlot to be exact) to print on screen
Loop through products to find the ids to use (typical format is: x,z (comma separated values)
Open Loop through years/months
Execute one sql query for each affected table/database to get the number of affected rows.
Close year loop
Send data to graph script (jquery jqPlot to be exact) to print on screen' AND dateMonitor<='1170284399' AND dateMonitor='test'
 UNION ALL
 SELECT COUNT(*) AS monthlyTotal FROM db1.table2 WHERE dateMonitor>='1170284401' AND dateMonitor<='1172703599' AND dateMonitor='test' ...
for ($m=1; $m<=12; $m++)
{
$startDate = mktime(0,1,$m,$myYear);
$daysOfMonth = date("t",mktime(10,10,$myYear));
$endDate = mktime(23,59,$daysOfMonth,$myYear);

$query_chk1 .= "SELECT COUNT(*) AS monthlyTotal FROM db1.table1 WHERE dateMonitor>='$startDate' AND dateMonitor<='$endDate' AND pageName='test' UNION ALL ";
}

$query_chk1 = substr($query_chk1,-10);