select top 15 com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName from Com_Product inner join (select userid,dir,html,comName from com_user where com_user.checked = 1 ) as com_user on com_user.userid=Com_Product.userid and ClassId=1 and NClassId=63 order by id desc 我需要积分 要不也没时间给你解决问题 o(∩_∩)o...哈哈
乱猜的,不知道下面这个方法效率怎么样.select top 15 com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName from Com_Product inner join com_user on com_user.userid=Com_Product.userid and ClassId=1 and NClassId=63 WHERE com_user.checked = 1 order by id desc
select top 15 com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName from (select * from Com_Product where ClassId=1 and NClassId=63 ) inner join (select* from com_user where checked=1) on com_user.userid=Com_Product.userid order by id desc 令:在checked Int(4) 上建立索引是没有用,数据的默读太大
select top 15 com.* from( select com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName checked=com_user.checked from Com_Product inner join com_user on com_user.userid=Com_Product.userid and ClassId=1 and NClassId=63 order by id desc )com where com.checked=1这样呢....
-- 加个索引看看 CREATE INDEX ix_UseID_checked ON com_user(UserID,checked)
CREATE INDEX ix_UseID_checked ON com_user(UserID,checked) 就是这么简历的
drop index ix_UseID_checked CREATE INDEX ix_UseID_checked ON com_user(UserID) 这样试试
分成二句看看. select top 15 com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName into #com_product from Com_Product where ClassId=1 and NClassId=63 and checked=1 order by id desc select * from #com_product inner join com_user on com_user.userid=#Com_Product.userid
还不行就分三句select com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName into #com_product from Com_Product where ClassId=1 and NClassId=63 and checked=1 select * into #com_product1 from #com_product order by id desc select top 15 * into ##com_product2 from #com_product1 select * from #com_product1 inner join com_user on com_user.userid=#Com_Product.userid
select com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName into #com_product from Com_Product where ClassId=1 and NClassId=63 and checked=1 select * into #com_product1 from #com_product order by id desc select top 15 * into #com_product2 from #com_product1 select * from #com_product2 inner join com_user on com_user.userid=#Com_Product.userid
and com_user.checked = 1 改为 and com_user.checked + 1 = 2强制不使用这个索引.
select top 15 com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName from Com_Product inner join com_user on com_user.userid=Com_Product.userid where Com_Product.ClassId=1 and Com_Product.NClassId=63 and com_user.checked = 1 在Com_Product的ClassID和NClassID上面分别建立索引,Order by 没必要的话就省略掉
写法无需改,只需加上正确的索引。不知道ClassId,NClassID和order by 的id是哪个表的?
create index ix_01 on Com_Product(NClassId,ClassId,checked,userid,id) create index ix_01 on com_user(userid)--如果有不必加
Com_Product.ClassId,Com_Product.NClassId,com_user.checked加上索引 select top 15 com_product.id as id, com_product.ClassID, com_product.NClassID, com_product.MClassID, com_product.img,com_product.Brand, com_product.topic, com_product.userid, com_product.html, com_product.dir, com_product.updatetime, com_user.dir as cdir, com_user.html as chtml, com_user.comName from Com_Product, com_user where Com_Product.ClassId=1 and Com_Product.NClassId=63 and com_user.checked = 1 and Com_Product.userid = com_user.userid order by id desc
值有 0,1,2 三个值!
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
from Com_Product
inner join (select userid,dir,html,comName
from com_user
where com_user.checked = 1
) as com_user
on com_user.userid=Com_Product.userid
and ClassId=1
and NClassId=63
order by id desc
我需要积分 要不也没时间给你解决问题 o(∩_∩)o...哈哈
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
from Com_Product
inner join com_user on com_user.userid=Com_Product.userid and ClassId=1 and NClassId=63
WHERE com_user.checked = 1
order by id desc
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
from (select * from Com_Product where ClassId=1 and NClassId=63 )
inner join (select* from com_user where checked=1) on com_user.userid=Com_Product.userid
order by id desc 令:在checked Int(4) 上建立索引是没有用,数据的默读太大
from(
select com_product.id as id,
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
checked=com_user.checked
from Com_Product
inner join com_user on com_user.userid=Com_Product.userid and ClassId=1 and NClassId=63
order by id desc )com
where com.checked=1这样呢....
2.看看com_user 表有没有索引碎片 dbcc showcontig(),如果有请整理
是数据分布问题造成的啦在com_user建一个索引效果会好一些。CREATE INDEX ix_UseID_checked ON com_user(UserID,com_user)
表: 'Com_User'(1103395050);索引 ID: 1,数据库 ID: 8
已执行 TABLE 级别的扫描。
- 扫描页数.....................................: 5967
- 扫描扩展盘区数...............................: 748
- 扩展盘区开关数...............................: 747
- 每个扩展盘区上的平均页数.....................: 8.0
- 扫描密度[最佳值:实际值]....................: 99.73%[746:748]
- 逻辑扫描碎片.................................: 0.00%
- 扩展盘区扫描碎片.............................: 64.97%
- 每页上的平均可用字节数.......................: 283.6
- 平均页密度(完整)...........................: 96.50%
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
-- 加个索引看看
CREATE INDEX ix_UseID_checked ON com_user(UserID,checked)
就是这么简历的
CREATE INDEX ix_UseID_checked ON com_user(UserID) 这样试试
select top 15 com_product.id as id,
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
into #com_product
from Com_Product
where
ClassId=1 and NClassId=63 and checked=1
order by id desc select * from #com_product
inner join com_user on com_user.userid=#Com_Product.userid
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
into #com_product
from Com_Product
where
ClassId=1 and NClassId=63 and checked=1
select * into #com_product1 from #com_product
order by id desc
select top 15 * into ##com_product2 from #com_product1
select * from #com_product1
inner join com_user on com_user.userid=#Com_Product.userid
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
into #com_product
from Com_Product
where
ClassId=1 and NClassId=63 and checked=1
select * into #com_product1 from #com_product
order by id desc
select top 15 * into #com_product2 from #com_product1
select * from #com_product2
inner join com_user on com_user.userid=#Com_Product.userid
改为
and com_user.checked + 1 = 2强制不使用这个索引.
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
from Com_Product
inner join com_user on com_user.userid=Com_Product.userid
where
Com_Product.ClassId=1 and Com_Product.NClassId=63
and com_user.checked = 1 在Com_Product的ClassID和NClassID上面分别建立索引,Order by 没必要的话就省略掉
create index ix_01 on com_user(userid)--如果有不必加
select top 15 com_product.id as id,
com_product.ClassID,
com_product.NClassID,
com_product.MClassID,
com_product.img,com_product.Brand,
com_product.topic,
com_product.userid,
com_product.html,
com_product.dir,
com_product.updatetime,
com_user.dir as cdir,
com_user.html as chtml,
com_user.comName
from Com_Product, com_user
where Com_Product.ClassId=1 and Com_Product.NClassId=63
and com_user.checked = 1
and Com_Product.userid = com_user.userid
order by id desc
把条件‘com_user.checked = 1 ’放在最前面(既紧跟inner join后面)就可以加快速度