页面执行时间太长，请各位大大帮忙看看
- WBOY原创
- 2016-06-23 14:17:19956浏览
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>订单统计</title> <link rel="stylesheet" type="text/css" href="css/ui-lightness/jquery-ui-1.8.2.custom.css" /><link rel="stylesheet" type="text/css" href="css/style.css" /> <script src="js/jquery.js" type="text/javascript"></script> <script src="js/i18n/grid.locale-ja.js" type="text/javascript"></script> <script type='text/javascript' src='js/picnet.table.filter.min.js'></script> <script type="text/javascript" src="js/jquery.ui.core.js"></script> <script type="text/javascript" src="js/jquery.ui.widget.js"></script> <script type="text/javascript" src="js/jquery.ui.position.js"></script> <script type="text/javascript" src="js/jquery.ui.autocomplete.js"></script><!--<link rel="stylesheet" type="text/css" href="css/css.css" />--><!--<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>--><!--<link rel="stylesheet" type="text/css" href="css/style.css" />--><style type="text/css"> * { padding:0; margin:0; } body { font-family:verdana; font-size:12px; margin-left: 0px; margin-top: 0px; } .content { width:1200px; margin:20px 10px; } .content h1 { font-family:'??'; font-size:18px; padding-bottom:5px; } table { width:100%; } th, td { padding:6px 0; } th { background-color:#accdf4; color:#ffffff; } tr { background-color:#FFFFFE; } .odd { background-color:#FFFAFF; } .highlight { background-color:#E0E0E0;}.STYLE1 {font-size: 14px}.STYLE2 {font-size: 16px}</style></head><body><?PHPclass runtime{ var $StartTime = 0; var $StopTime = 0; function get_microtime() { list($usec, $sec) = explode(' ', microtime()); return ((float)$usec + (float)$sec); } function start() { $this->StartTime = $this->get_microtime(); } function stop() { $this->StopTime = $this->get_microtime(); } function spent() { return round(($this->StopTime - $this->StartTime) * 1000, 1); } }$runtime= new runtime;$runtime->start();include("conn.php");$to_date=date('Y-m-d');//按包装区域来统计邮件总数function sale_address_package_statistics($date,$account,$package){$sql="select `number` from `sale_orders` where `account`='$account' and date(`upload_date`) ='$date' GROUP BY `number`";$Result = mysql_query($sql);$number="(`number`='' ";while($rs = mysql_fetch_array($Result)){$number.=" or `number`=".$rs[0];}$number.=")";$sql_order_address="SELECT * FROM `sale_orders` T ";$sql_order_address.="WHERE ".$number;$sql_order_address.="AND `account` ='$account' ";if($package=='MIX'){$sql_order_address.="AND 1<(select count(*) from `sale_orders` where ".$number." AND `account` ='$account' AND `recipient_name`=T.`recipient_name` AND `ship_address_1`=T.`ship_address_1`) ";}else{$sql_order_address.="AND 1=(select count(*) from `sale_orders` where ".$number." AND `account` ='$account' AND `recipient_name`=T.`recipient_name` AND `ship_address_1`=T.`ship_address_1`) ";$sql_order_address.="AND `package_area`='$package' ";}$sql_order_address.="GROUP BY `recipient_name` , `ship_address_1`";$result_label = mysql_query($sql_order_address);$order_address=mysql_affected_rows();if(empty($order_address)){return 0;}else{return $order_address;}}?><h2 class="top_title">订单统计</h2><div class="digg"> <table width="200" border="0" cellspacing="0"> <tr> <td width="72%"><table width="95%" border="0" cellspacing="1" bgcolor="#dddddd"> <tr> <td width="15%" align="center"> </td> <td align="center" bgcolor="eeeeee" colspan="3">PM_AC</td> <td align="center" bgcolor="eeeeee" colspan="3">CO_AC</td> <td align="center" bgcolor="eeeeee" colspan="3">UK_AC</td> <td align="center" bgcolor="eeeeee" colspan="3">UK_FR</td> <td align="center" bgcolor="eeeeee" colspan="3">UK_DE</td> </tr> <tr> <td width="25%" align="center"> </td> <td width="5%" align="center" bgcolor="eeeeee">SZ</td> <td width="5%" align="center" bgcolor="eeeeee">HK</td> <td width="5%" align="center" bgcolor="eeeeee">MIX</td> <td width="5%" align="center" bgcolor="eeeeee">SZ</td> <td width="5%" align="center" bgcolor="eeeeee">HK</td> <td width="5%" align="center" bgcolor="eeeeee">MIX</td> <td width="5%" align="center" bgcolor="eeeeee">SZ</td> <td width="5%" align="center" bgcolor="eeeeee">HK</td> <td width="5%" align="center" bgcolor="eeeeee">MIX</td> <td width="5%" align="center" bgcolor="eeeeee">SZ</td> <td width="5%" align="center" bgcolor="eeeeee">HK</td> <td width="5%" align="center" bgcolor="eeeeee">MIX</td> <td width="5%" align="center" bgcolor="eeeeee">SZ</td> <td width="5%" align="center" bgcolor="eeeeee">HK</td> <td width="5%" align="center" bgcolor="eeeeee">MIX</td> </tr> <tr> <td align="center" bgcolor="eeeeee"><span class="STYLE1"><?PHP echo $to_date;?></span></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'PM_AC','SZ'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'PM_AC','HK'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'PM_AC','MIX'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'CO_AC','SZ'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'CO_AC','HK'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'CO_AC','MIX') ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_AC','SZ'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_AC','HK'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_AC','MIX'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_FR','SZ'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_FR','HK'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_FR','MIX'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_DE','SZ'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_DE','HK'); ?></td> <td align="center"><?PHP echo sale_address_package_statistics($to_date,'UK_DE','MIX'); ?></td> </tr> </table> <?PHP $runtime->stop(); $runtimea= new runtime;$runtimea->start(); ?> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p></td> <td width="28%"></td> </tr></table></div><?PHP $runtimea->stop();echo "邮件统计页面执行时间: ".$runtime->spent()." 毫秒,订单统计页面执行时间:".$runtimea->spent()." 毫秒"; ?></body></html> 这个页执行要75秒左右，这还只是显示一天的数据，实际上我需要显示一个月的数据
主要是那个查询语句的问题，以下语句如果查询的记录在1700条左右的时候，需要35秒
SELECT * FROM `sale_orders` T
WHERE (`number`='' or `number`=81)
AND 1=(select count(*) from `sale_orders`
where (`number`='' or `number`=81)
AND `recipient_name`=T.`recipient_name`
AND `ship_address_1`=T.`ship_address_1`)
AND `package_area`='SZ'
GROUP BY `recipient_name` , `ship_address_1`
请问怎么优化啊？显示一天的数据需要75秒，那一个月的数据不是等吃一餐饭还没显示完
回复讨论(解决方案)
具体情况需具体分析，毕竟不是在做文字游戏
调试查询指令应听从 EXPLAIN 的建议
尽量少用select * ，需要哪个字段查哪个，还有，加索引
数据表需要优化。
可否desc给大家看看呢
声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
上一篇：如何通过变量的形式向类中的构造函数传值下一篇：求一个正则，有点难，高手进