select distinct cpt1.artifactid From conceptproduct cpt1 use index (primary)
Join conceptproduct cpt2 on cpt1.artifactid=cpt2.artifactid
Join conceptproduct cpt3 on cpt2.artifactid=cpt3.artifactid
Where cpt1.term=5426 and cpt1.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
and cpt2.term=426 and cpt2.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
and cpt3.source not in(4,5,7,11,12,112,111,13,113,15,19,20) and
cpt3.term in (95823,31556,5572,40423) and cpt1.themeid<>cpt2.themeid or cpt1.source=21 or cpt2.source=21
order by cpt1.weight+cpt2.weight+cpt3.weight desc limit 0,20
这段是原有系统的sql但是执行速度相当慢 ,大概需要半个小时,转换为
select a.* from
(
select `Type`, `ArtifactID`, `Term`, `Weight`, `ThemeID`,
`Source` From conceptproduct cpt1
where cpt1.term=5426 and cpt1.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
) a join(select `Type`, `ArtifactID`, `Term`, `Weight`, `ThemeID`,
`Source` From conceptproduct cpt2
where cpt2.term=426 and cpt2.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
) b on a.artifactid=b.artifactid
join (
select `Type`, `ArtifactID`, `Term`, `Weight`, `ThemeID`,
`Source` From conceptproduct cpt3
where cpt3.source not in(4,5,7,11,12,112,111,13,113,15,19,20) and cpt3.term in (95823,31556,5572,40423)
) c on b.artifactid=c.artifactid
where a.themeid<>b.themeid or a.source=21 or b.source=21
order by a.weight+b.weight+c.weight desc limit 0,20
同样的环境下,但是结果为空。只有
select `Type`, `ArtifactID`, `Term`, `Weight`, `ThemeID`,
`Source` From conceptproduct cpt1
where cpt1.term=5426 and cpt1.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
只有执行有结果。为什么这2段的sql语句的执行结果不一样呢,请高手帮忙分析下。
Join conceptproduct cpt2 on cpt1.artifactid=cpt2.artifactid
Join conceptproduct cpt3 on cpt2.artifactid=cpt3.artifactid
Where cpt1.term=5426 and cpt1.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
and cpt2.term=426 and cpt2.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
and cpt3.source not in(4,5,7,11,12,112,111,13,113,15,19,20) and
cpt3.term in (95823,31556,5572,40423) and cpt1.themeid<>cpt2.themeid or cpt1.source=21 or cpt2.source=21
order by cpt1.weight+cpt2.weight+cpt3.weight desc limit 0,20
这段是原有系统的sql但是执行速度相当慢 ,大概需要半个小时,转换为
select a.* from
(
select `Type`, `ArtifactID`, `Term`, `Weight`, `ThemeID`,
`Source` From conceptproduct cpt1
where cpt1.term=5426 and cpt1.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
) a join(select `Type`, `ArtifactID`, `Term`, `Weight`, `ThemeID`,
`Source` From conceptproduct cpt2
where cpt2.term=426 and cpt2.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
) b on a.artifactid=b.artifactid
join (
select `Type`, `ArtifactID`, `Term`, `Weight`, `ThemeID`,
`Source` From conceptproduct cpt3
where cpt3.source not in(4,5,7,11,12,112,111,13,113,15,19,20) and cpt3.term in (95823,31556,5572,40423)
) c on b.artifactid=c.artifactid
where a.themeid<>b.themeid or a.source=21 or b.source=21
order by a.weight+b.weight+c.weight desc limit 0,20
同样的环境下,但是结果为空。只有
select `Type`, `ArtifactID`, `Term`, `Weight`, `ThemeID`,
`Source` From conceptproduct cpt1
where cpt1.term=5426 and cpt1.source not in(4,5,7,11,12,112,111,13,113,15,19,20)
只有执行有结果。为什么这2段的sql语句的执行结果不一样呢,请高手帮忙分析下。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货