数据库:mysql.
数据记录:158000条
对数据进行查询统计,统计费用字段,按时间段和区域进行横向统计。
查询的时候PHP页面显示超时。大概有一分钟的样子。
请问有没有更好的办法解决速度问题????
数据记录:158000条
对数据进行查询统计,统计费用字段,按时间段和区域进行横向统计。
查询的时候PHP页面显示超时。大概有一分钟的样子。
请问有没有更好的办法解决速度问题????
解决方案 »
- 关于页面跳转的问题
- 对于SQL语句为select、update、insert、delete时,执行后$result返回什么?
- include和require有什么区别(初级问题)
- php如何配置类似于iis中的虚拟目录
- 请教一个问题,我在A公司注册的域名现在快到期了,我能不能到B公司续费呢?
- PHP中调用WebService接口,谁给个详细代码呢?
- 一个SQL关联查询的问题。求解
- 用户在一台电脑上已经登陆成功,在未退出或关闭浏览器前就不能在另一台电脑上登陆,PHP怎么做这个啊??
- php+mysql适合我这样的网站吗?
- 熟悉网络搜索技术的朋友进来
- memcached问题
- 请教下各位:像CSDN论坛这种登录时可勾选的两周内免登录实现
http://dev.mysql.com/doc/refman/5.1/zh/optimization.html
include_once('conn.php');
include_once('../include/calendar.php');
$sql='select ID,station_name from end_station';
$result=mysql_db_query($MYSQL_DB,$sql,$conn);
if($_POST['tj_date']==NULL)
{
$tj_date=date('Y-m-d',time());
}
else
{
$tj_date=$_POST['tj_date'];
}
if($_POST['tj_date2']==NULL)
{
$tj_date2=date('Y-m-d',time());
}
else
{
$tj_date2=$_POST['tj_date2'];
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>..:: 货运运单管理系统 ::..正式版</title>
<link href="../system/images/style.css" rel="stylesheet" type="text/css">
<style type="text/css">
table{background-color:#999999;}
td{height:22px;padding:5px;}
</style>
</head>
<body>
<div id="page-header"><img src="images/font_win.gif" width="16" height="16">当前位置:报表中心>日收货量及线路明细汇总</div>
<center>
<fieldset style="width:99%; padding:5px;">
<legend>统计条件</legend>
<form name="form1" method="post" action="tj.php" style="text-align:left;">
<strong>开始日期:</strong>
<input name="tj_date" type="text" id="tj_date" onClick="fPopCalendar(tj_date,tj_date);return false">
<strong>结束日期:</strong>
<input name="tj_date2" type="text" id="tj_date2" onClick="fPopCalendar(tj_date2,tj_date2);return false">
<strong>线路:</strong>
<select name="end_station" id="end_station">
<option value="0">==选择线路==</option>
<?
while($row=mysql_fetch_array($result))
{
?>
<option value="<?=$row['ID']?>"><?=$row['station_name']?></option>
<?
}
mysql_free_result($result);
?>
</select>
<input type="submit" name="Submit" value="统计">
</form>
</fieldset>
<fieldset style="width:99%; padding:5px;text-align:left;">
<legend>统计结果</legend>
<?
$sql='select ID from start_station order by id asc';
$result=mysql_db_query($MYSQL_DB,$sql,$conn);
?>
<div style="width:90%; text-align:left; height:15px;padding:10px;"><strong>线路:
<?
if($_POST['end_station']==NULL)
{
$sql5='select station_name from end_station where id=1';
}
else
{
$sql5='select station_name from end_station where id='.$_POST['end_station'];
}
$result5=mysql_db_query($MYSQL_DB,$sql5,$conn);
$row5=mysql_fetch_array($result5);
echo $row5['station_name'];
mysql_free_result($result5);
echo " 统计日期:".$tj_date." 到 ".$tj_date2;
?></strong>
</div>
<table width="90%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td bgcolor="#FFFFFF"> </td>
<?
while($row=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#CCCCCC"><strong>
<?=$row['station_name']?>
</strong></td>
<?
}
mysql_data_seek($result,0);
?>
<td align="center" bgcolor="#CCCCCC"><strong>总计</strong></td>
</tr>
<tr>
<td align="center" bgcolor="#CCCCCC"><strong>回 单</strong></td>
<?
//进行回单统计
if($_POST['end_station']==NULL)
{
$end_station=1;
}
else
{
$end_station=$_POST['end_station'];
}
while($row1=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF">
<?
$sql2='select sum(trans_fee) from documents where start_station='.$row1['ID'].' and end_station='.$end_station.' and pay_type=\'回单\' and (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\')';
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?>
<?
}
?>
<td align="center" bgcolor="#FFFFFF">
<?
//回单总计
$sql3='select sum(trans_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and pay_type=\'回单\' and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
<tr>
<td align="center" bgcolor="#CCCCCC"><strong>现 付</strong></td>
<?
mysql_data_seek($result,0);
//进行收现金统计
if($_POST['end_station']==NULL)
{
$end_station=1;
}
else
{
$end_station=$_POST['end_station'];
}
while($row8=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF">
<?
$sql2='select sum(trans_fee) from documents where start_station='.$row8['ID'].' and end_station='.$end_station.' and pay_type=\'现付\' and (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\')';
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?> </td>
<?
}
?>
<td align="center" bgcolor="#FFFFFF">
<?
//现金总计
$sql3='select sum(trans_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and pay_type=\'现付\' and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
<tr>
<td align="center" bgcolor="#CCCCCC"><strong>合 计</strong></td>
<?
mysql_data_seek($result,0);
//进行收现金与回单合计统计
if($_POST['end_station']==NULL)
{
$end_station=1;
}
else
{
$end_station=$_POST['end_station'];
}
while($row9=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF">
<?
$sql2='select sum(trans_fee) from documents where start_station='.$row9['ID'].' and end_station='.$end_station.' and (pay_type=\'现付\' or pay_type=\'回单\') and (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\')';
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?> </td>
<?
}
?>
<td align="center" bgcolor="#FFFFFF">
<?
//现金与回单总计
$sql3='select sum(trans_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and (pay_type=\'现付\' or pay_type=\'回单\') and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
<tr>
<td align="center" bgcolor="#CCCCCC"><strong>提 付</strong></td>
<?
mysql_data_seek($result,0);
//进行提付统计
if($_POST['end_station']==NULL)
{
$end_station=1;
}
else
{
$end_station=$_POST['end_station'];
}
while($row10=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF">
<?
$sql2='select sum(trans_fee) from documents where start_station='.$row10['ID'].' and end_station='.$end_station.' and pay_type=\'提付\' and (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\')';
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?> </td>
<?
}
?>
<td align="center" bgcolor="#FFFFFF">
<?
//提付总计
$sql3='select sum(trans_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and pay_type=\'提付\' and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
<tr>
<td align="center" bgcolor="#CCCCCC"><strong>垫付款</strong></td>
<?
mysql_data_seek($result,0);
while($row10=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF">
<?
$sql2='select sum(dian_fee) from documents where start_station='.$row10['ID'].' and end_station='.$end_station.' and (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\')';
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?>
</td>
<?
}
?>
<td align="center" bgcolor="#FFFFFF"><?
//垫付总计
$sql3='select sum(dian_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
<td align="center" bgcolor="#CCCCCC"><strong>代收款</strong></td>
<?
mysql_data_seek($result,0);
while($row10=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF"><?
$sql2='select sum(dai_fee) from documents where start_station='.$row10['ID'].' and end_station='.$end_station.' and (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\')';
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?></td>
<?
}
?>
<td align="center" bgcolor="#FFFFFF"><?
//代收总计
$sql3='select sum(dai_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
<tr>
<td align="center" bgcolor="#CCCCCC"><strong>垫代合计</strong></td>
<?
mysql_data_seek($result,0);
//进行垫贷合计统计
if($_POST['end_station']==NULL)
{
$end_station=1;
}
else
{
$end_station=$_POST['end_station'];
}
while($row10=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF">
<?
$sql2='select sum(dian_fee)+sum(dai_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and start_station='.$row10['ID'].' and end_station='.$end_station;
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?> </td>
<?
}
?>
<td align="center" bgcolor="#FFFFFF">
<?
//提付总计
$sql3='select sum(dian_fee)+sum(dai_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
<tr>
<td align="center" bgcolor="#CCCCCC"><strong>送 货</strong></td>
<?
mysql_data_seek($result,0);
//进行提付统计
if($_POST['end_station']==NULL)
{
$end_station=1;
}
else
{
$end_station=$_POST['end_station'];
}
while($row10=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF">
<?
$sql2='select sum(send_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and start_station='.$row10['ID'].' and end_station='.$end_station;
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?> </td>
<?
}
?>
<td align="center" bgcolor="#FFFFFF">
<?
//提付总计
$sql3='select sum(send_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
<tr>
<td align="center" bgcolor="#CCCCCC"><strong>总 计</strong></td>
<?
mysql_data_seek($result,0);
//进行现金总统计
if($_POST['end_station']==NULL)
{
$end_station=1;
}
else
{
$end_station=$_POST['end_station'];
}
while($row10=mysql_fetch_array($result))
{
?>
<td align="center" bgcolor="#FFFFFF">
<?
$sql2='select sum(trans_fee)-sum(send_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and start_station='.$row10['ID'].' and end_station='.$end_station;
$result2=mysql_db_query($MYSQL_DB,$sql2,$conn);
$row2=mysql_fetch_row($result2);
if($row2[0]==NULL)
{
echo '0';
}
else
{
echo $row2[0];
}
mysql_free_result($result2);
?> </td>
<?
}
?>
<td align="center" bgcolor="#FFFFFF">
<?
//提付总计
$sql3='select sum(trans_fee)-sum(send_fee) from documents where (create_date between \''.$tj_date.'\' and \''.$tj_date2.'\') and end_station='.$end_station;
$result3=mysql_db_query($MYSQL_DB,$sql3,$conn);
$row3=mysql_fetch_row($result3);
if($row3[0]==NULL)
{
echo '0';
}
else
{
echo $row3[0];
}
mysql_free_result($result3);
?></td>
</tr>
</table>
<form name="form2" method="post" action="print.php" style="text-align:right" target="_blank">
<input name="tj_date" type="hidden" id="tj_date" value="<?=$tj_date?>">
<input name="tj_date2" type="hidden" id="tj_date2" value="<?=$tj_date2?>">
<input name="end_station" type="hidden" id="end_station" value="<?=$_POST['end_station']?>">
<input type="submit" name="Submit2" value="打印输出报表">
</form>
</fieldset>
</body>
</html>
2.优化一下 MYSQL.INI
3.检查一下自己的SQL语句 尽量不要使用 LIKE
...
mysql_select_db和mysql_query来代替它,具体用法百度找下。很简单的。