【www.gdgbn.com--mysql教程】
mysql教程 limit 用法与mysql limit 优化
/*
mysql limit 用法2007年11月08日 14:291. select * from tablename <条件语句> limit 100,15
从100条记录后开始取15条 (实际取取的是第101-115条数据)
2. select * from tablename <条件语句> limit 100,-1
从第100条后开始-最后一条的记录
3. select * from tablename <条件语句> limit 15
相当于limit 0,15 .查询结果取前15条数据
下面来看看limit 优化方法,首先保存1000000条记录如下。
*/
set_time_limit(0);
mysql_connect("localhost","root","root");
mysql_select_db("accc");
mysql_query("set names "gb2312"");
echo "正在导入数据....";
for( $i=0;$i<=1000000;$i++)
{
$sql ="insert cn_loupan_news(cityid,zid,tid,did,title,body,click,litpic,sendname,header,recommend,hot,flash,setindex,opencomment,cityname,keywords,desciption,color,b,senddate,lid,nfrom,title2,zt)values("11","2","1","1","测试数据","测试数据","88","/aa/gigf.gif","aaaa","0","0","0","0","0","0","222","222","gsfdsgfds","1","1","124567124","0","fdafda","f","fdafa")";
mysql_query($sql) or die(mysql_error());
}
echo "成功导入100万数据";
/*
select id
from `cn_loupan_news`
order by id desc
limit 800001 , 20
按主关键词排排序查询时间为 显示行 0 - 19 (20 总计, 查询花费 0.2712 秒)select *
from `cn_loupan_news`
order by id desc
limit 800001 , 20
显示行 0 - 19 (20 总计, 查询花费 4.3221 秒)
从这里看如何选择id主索引的,排序速度还理想,假如是*号再带排序查询时间4秒多啊,不可取的方法。
select *
from cn_loupan_news
where id >= (
select id
from cn_loupan_news
limit 800000 , 1 )
limit 10
显示行 0 - 9 (10 总计, 查询花费 0.2456 秒)
稍稍好了一点。
本站原创文章转载注明来源于http://www.111cn.net/database/database.html