15万条数据的查询

szdhsoft 2011-05-08 09:44:40

数据库：mysql.
数据记录：158000条
对数据进行查询统计，统计费用字段，按时间段和区域进行横向统计。
查询的时候PHP页面显示超时。大概有一分钟的样子。
请问有没有更好的办法解决速度问题？？？？

...全文

341 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

quanzi0214 2011-05-10

打赏
举报

优化数据库吧，最好把时间字段用时间戳保存，int做索引还是很快的！优化得当0.1秒内绝对能查询完成！

转身 2011-05-09

打赏
举报

你自己可以用explain来看看sql语句的好坏

转身 2011-05-09

打赏
举报

表documents中加一个trans_fee，start_station，end_station的联合索引

转身 2011-05-09

打赏
举报

首先这句'select ID from start_station order by id asc' 把order by id asc去掉，数据库默认就是升序的，使用order by很难用到索引，并且速度慢很多

kaifadi 2011-05-09

打赏
举报

是SQL语句问题，像楼上所说。开始的查询并没有条件。等于15万的ID一起获得。

而且这个mysql_db_query在PHP4以后就不推荐使用了。你的代码里居然还在使用。用
mysql_select_db和mysql_query来代替它，具体用法百度找下。很简单的。

helloyou0 2011-05-09

打赏
举报

所有表的表结构，
你里面多个表，哪个的数据是15万？
其它表里数据多少？

查这种性能问题，最好是自己做测试，不要想当然，
查出具体哪一部分的速度太慢，再做优化，

黄袍披身 2011-05-09

打赏
举报

1.检查一下硬盘
2.优化一下 MYSQL.INI
3.检查一下自己的SQL语句尽量不要使用 LIKE
...

szdhsoft 2011-05-09

打赏
举报

szdhsoft 2011-05-09

打赏
举报

</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($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>

szdhsoft 2011-05-09

打赏
举报

<?
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);
?>

kaifadi 2011-05-09