我现在只查询上个月第一天开始的数据,limit加在group by 后面,好像还是很慢。我也有建索引。 SELECT com_cn_stock.stockNum,com_cn_supply.syShortName,com_cn_user.real_name,com_cn_stock.Quantity,com_cn_stock.amount,com_cn_stock.submit_date,com_cn_stock.state,com_cn_stock.shengorder,com_cn_product.Title_CN,com_cn_stock.orderType FROM com_cn_stoc JOIN com_cn_stock_detail ON com_cn_stock.stockNum = com_cn_stock_detail.stockNum JOIN com_cn_product ON com_cn_stock_detail.itemNum = com_cn_product.ItemNum JOIN com_cn_supply ON com_cn_stock.syNum = com_cn_supply.syNum JOIN com_cn_user ON com_cn_stock.usernum = com_cn_user.usernum where com_cn_stock.state<3 and com_cn_stock.submit_date <='2012-05-01' group by com_cn_stock.stockNum limit 100,ORDER BY com_cn_stock.stockNum DESC
" FROM com_cn_stock" .
" LEFT JOIN com_cn_stock_detail ON com_cn_stock.stockNum = com_cn_stock_detail.stockNum" .
" LEFT JOIN com_cn_product ON com_cn_stock_detail.itemNum = com_cn_product.ItemNum" .
" JOIN com_cn_supply ON com_cn_stock.syNum = com_cn_supply.syNum" .
" JOIN com_cn_user ON com_cn_stock.usernum = com_cn_user.usernum ".$strWhere.' group by com_cn_stock.stockNum'.$strOrder);//执行SQL语句
$arrInfo = $rs->fetchAll();//返回结果
$arrData = array_slice($arrInfo,($_GET['page']-1)*$arrGPage['page_size'],$arrGPage['page_size']);//截取当页显示的数组
读取那么多数据... 幸好你只有1000条数据...
还是直接使用 limit 分页吧.
$p = 1;
$_GET['p']&&$p = $_GET['p'];//第几页
$pagenum = 20;//每页20数据select title form test limit $p-1*20,$p*20;// 获取当前页的起始 至 当前页的结束位置数据。
业务要求是要读取这些表的数据。我加了LIMIT,还是很慢。
我把LIMIT 加在最后。但是我觉得这样没起多在作用。可能我加的位置不对吧。请帮忙指点一下
SELECT com_cn_stock.stockNum,com_cn_supply.syShortName,com_cn_user.real_name,com_cn_stock.Quantity,com_cn_stock.amount,com_cn_stock.submit_date,com_cn_stock.state,com_cn_stock.shengorder,com_cn_product.Title_CN,com_cn_stock.orderType FROM com_cn_stoc
JOIN com_cn_stock_detail ON com_cn_stock.stockNum = com_cn_stock_detail.stockNum
JOIN com_cn_product ON com_cn_stock_detail.itemNum = com_cn_product.ItemNum
JOIN com_cn_supply ON com_cn_stock.syNum = com_cn_supply.syNum
JOIN com_cn_user ON com_cn_stock.usernum = com_cn_user.usernum where com_cn_stock.state<3 and com_cn_stock.submit_date <='2012-05-01' group by com_cn_stock.stockNum limit 100,ORDER BY com_cn_stock.stockNum DESC
自己瞎猜是不行的,随意减少关联的表可能导致查询结果不准确