我被分配了一些任务,即从一些已保存的数据中为网站创建一些图形统计数据.
事实:
– 有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 COUNT(*) AS monthlyTotal FROM db1.table1 WHERE dateMonitor>='11676Loop 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
' AND dateMonitor<='1170284399' AND dateMonitor='test'
UNION ALL
SELECT COUNT(*) AS monthlyTotal FROM db1.table2 WHERE dateMonitor>='1170284401' AND dateMonitor<='1172703599' AND dateMonitor='test' ...
解决方法:
你可以做的并不多,至少因为你已经为所有专栏编制了索引……这是我能想到的最好的:
SELECT COUNT(*)
FROM `stats`
WHERE `productID IN(1,2,3)
AND `dateMonitor` >=
AND `dateMonitor` <=
AND `pageName`=''
… 如何:
>正如ypercube评论的那样 – 使用COUNT(*)更快.
>我肯定不知道这一点,但我相信使用> =和< =而不是BETWEEN来表示整数应该更快.
您应该尝试的另一件事是一次执行所有查询(如果多于一个).用文字正确解释它会更难,我看到你对SQL有很好的把握,所以你应该能够得到逻辑,所以这是一个例子:
假设我们需要搜索2006年5月到2008年4月期间123,13,5和6的ID的产品,以及pageName’test’:
>我们在生成查询之前计算时间戳,并确切地确定我们需要搜索哪些表.
SELECT COUNT(*)AS myCounter
来自stats2006
产品ID IN(5,6,13,123)
AND dateMonitor> = 1146430800
AND pageName =’test’
/ *这里我们只需要检查2006年5月1日00:00:00 * /的时间戳
UNION ALL
SELECT COUNT(*)AS myCounter
来自stats2007
产品ID IN(5,6,13,123)
AND pageName =’test’
/ *这里我们不需要检查dateMonitor字段
因为全年与我们的时期相符
* /
UNION ALL
SELECT COUNT(*)AS myCounter
来自stats2008
产品ID IN(5,6,13,123)
AND dateMonitor< = 1209589199
AND pageName =’test’
/ *这里我们只需要查看2008年4月30日23:59:59 * /的时间戳
标签:php,optimization,mysql
来源: https://codeday.me/bug/20190709/1418535.html