显示结果很慢
显示结果很慢
提问于 2015-04-24 16:29:50
我有一个Windows7系统，Wamp 2.4运行这个简单的php代码
for ($year = 2004; $year <= 2015; $year++){
for ($month = 1; $month <=12; $month++){
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$result = mysql_query("SELECT sum(Weight) FROM Lab WHERE YEAR(date)=$year AND MONTH(date)= $month") or die(mysql_error());
while ($rows = mysql_fetch_array($result)) {
$weight = $rows['sum(Weight)'];
${'mes'.$month}=$weight;
$weight3=$weight+$weight3;
$weight=0;
}
}
Echo $year, " Total - ", $weight3, "<br>";
Echo $mes1, ", ", $mes2, ", ", $mes3, ", ", $mes4, ", ", $mes5, ", ", $mes6, ", ", $mes7, ", ", $mes8, ", ", $mes9, ", ", $mes10, ", ", $mes11, ", ", $mes12, "<br>";
$weight3=0;
}我的数据库大约有400000行，由于某种原因，显示结果需要大约40秒。如果我在mysql中执行相同的查询(年复一年)，则所需时间不到一秒钟。
我的代码有什么问题使查询慢下来了吗？或者这是一个与沼泽有关的问题？
谢谢你的帮助！
最终代码
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$result = mysql_query("SELECT year(date), month(date), sum(Weight)
FROM Lab
WHERE date BETWEEN '2004-01-01' AND '2015-12-31'
GROUP BY year(date), month(date)");
while ($rows = mysql_fetch_array($result)) {
echo $rows ['year(date)']," ", $rows ['month(date)']," ",$rows['sum(Weight)'];
Echo "<br>";
} 回答 1
Stack Overflow用户
回答已采纳
发布于 2015-04-24 16:34:02
这段代码效率极低。您连接到DB 11年*12个月= 132次。循环也是不可缺少的。为什么不直接在查询中全部完成呢？
SELECT year(datefield), month(datefield), sum(weight)
FROM youtable
WHERE datefield BETWEEN '2004-01-01' AND '2015-12-31'
GROUP BY year(datefield), month(datefield)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29852794
复制相关文章
相似问题
腾讯云开发者
