两条SQL语句,一条查询结果集,一条查询记录数,为什么查询结果集的要比查询记录数的要快,不理解,求高人指及点解决方案查询结果集的语句:
select gq_id,bt,a17,gq.hy_id,lb,lxfs,gq_name,ip,hygq_dj,gq.gx_a17,djcs,cx_dj from
gq left join hy_gq on gq.hy_id=hy_gq.hy_id where fb_bs='Y' and hf_id=0 limit 0,50统计一共多少条记录数语句,用于分页
select count(*) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0两条语句的条件都是一样的,我觉得应该是第一条要慢于第二条,但实际情况相反!!求解决方案~~
select gq_id,bt,a17,gq.hy_id,lb,lxfs,gq_name,ip,hygq_dj,gq.gx_a17,djcs,cx_dj from
gq left join hy_gq on gq.hy_id=hy_gq.hy_id where fb_bs='Y' and hf_id=0 limit 0,50统计一共多少条记录数语句,用于分页
select count(*) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0两条语句的条件都是一样的,我觉得应该是第一条要慢于第二条,但实际情况相反!!求解决方案~~
应该是第一条慢吧,因为你限制的记录数,故感觉第二条慢
,速度更快
不修改SQL语句,不能做到,比如去掉LIMIT OR 加大 LIMIT中的数字
OR
select count(gq.hy_id) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_idORselect * from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_idSELECT FOUND_ROWS()
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_idSELECT FOUND_ROWS()
速度应该更慢
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_id SELECT FOUND_ROWS() 这么执行能行吗,先把记录查询出来,然后在统计记录数~~
感觉不会快!!
select count(gq.hy_id) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_id
就行了,如果记录多的话,速度不会一样,毕竟统计要经过两个步骤,生成临时表、统计,
查询直接显示临时表就OK了,而且有LIMIT,要快得多
select count(gq.hy_id) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_id
比
COUNT(*)要快一些
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_id 这样执行,是出来多条记录啊,然后我在把这些条记录的结果相加吗,是这个意思吗?
MSN:[email protected]
WWWWB
这两个ID是同一个主人的吗?
这样
select count(*) from (
select count(gq.hy_id) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_id ) aor
select sum(1) from (
select count(gq.hy_id) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_id ) a
count(gq.hy_id)是每个会员的记录之和
能加我的联系方式吗,这里不太方便
select count(gq.hy_id) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_id ) a这样能快吗?难道会比
select count(*) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 它快不理解
select sum(1) from (
select count(gq.hy_id) as count from gq left join hy_gq on gq.hy_id=hy_gq.hy_id
where fb_bs='Y' and hf_id=0 GROUP BY gq.hy_id ) a这条语句也不快
COUNT(主键)速度>COUNT(*)实际上道理已经说过,统计要经过两个阶段,而查询只有一个,且有LIMIT,速度怎么会一样?
COUNT(主键)速度>COUNT(*) 这个也试过,没有明显变化,速度差不多能不能从配置上优化呢,或者其它解决方法难道解决不了了
两个表不是一对一的关系,这条路行不通了表的索引情况
gq表的索引
gq_id 主健
hy_id 会员ID
hf_id 是否为主帖
bt 标题
a17 发布时间
nr 内容
gx_a17 更新时间hy_gq表的索引hy_id 主键
hygq_dj 会员等级
执行的时间正比于匹配的记录条数。
如果条数相当之巨大,那么count的操作比limit慢是当然的。如果你想让count快一点的话,建议使用cache,具体做法:
1 先用limit 0,20 取出分页第一页的结果
2 如果结果数少于20,则不用再count了,总数你已经知道
3 如果结果数==20,
3.1则去cache中询问一下count值,如果有值,使用cache中的值
3.2否则去数据库中查询一下count,并写入cache
4 得到了count值,可以写分页代码了
统计要经过两个步骤,生成临时表、统计,
查询直接显示临时表就OK了,而且有LIMIT,要快得多同样的数据量、SQL语句,MSSQL执行统计速度>显示的速度?不会吧,除非你限制显示条数(top)。