select * from recordings where pid in (select pid from recordings where kit like ‘%aa%’)select * from (select * from recordings where pid in (select recording_id from provisions where channel_name=’cmcc’)) as tb1 where pid in (select pid from recordings where kit like ‘%aa%’)
2 使用exists可能会快点。
3 给SQL EXPLAIN 内容
from recordings a
where exists (select pid from recordings where a.pid=pid and kit like '%aa%');或者select a.*
from recordings a inner join recordings b on a.pid=b.pid
where b.kit like '%aa%';具体优化的时候用explain看一下执行计划,以找到最优的方法。
2,最主要是优化你的%aa%,此类操作MYISAM引擎有全文索引,不过INNODB引擎处理此类查询更快!
on a.pid=b.pid where b.kit like ‘%aa%’)
select * from (select * from recordings where pid in (select recording_id from provisions where channel_name=’cmcc’)) as tb1 where pid in (select pid from recordings where kit like ‘%aa%’)
select * from (select a.* from recordings a inner join provisions b
on a.pid=b.recording_id
where b.cannel_name=’cmcc’)) as tb1
inner join recordings c on tb1.pid=c.pid
where c.kit like ‘%aa%’在连接字段上建立索引
select a.* from recordings a, (select distinct pid from recordings where kit like '%aa%') b where a.pid=b.pid select * from (select a,* from recordings a,(select recording_id from provisions where channel_name='cmcc') b where a.pid=b.recording_id ) as tb1
,(select distinct pid from recordings where kit like '%aa%') b
where a.pid=b.pid
如此优化!
我觉得你可以考虑其他条件查询。2 Kit 字段是大对象之类内容更加麻烦,查询需要资源更加是一个无底洞。
on a.pid=b.pid where b.kit like ‘%aa%’)
的explain结果是:
select_type:simple simple
table:a b
type:all ref
possible_keys:idx_pid idx_pid
key:null idx_pid
key_len:null 9
ref:null mind_work.a.pid
rows:296720 2
运行时间是1.642s
select * from (select a.* from recordings a inner join provisions b
on a.pid=b.recording_id
where b.cannel_name=’cmcc’)) as tb1
inner join recordings c on tb1.pid=c.pid
where c.kit like ‘%aa%’
的explain结果是:
select_type:primary primary derived derived
table:<derived2> c b a
type:all ref all ref
possible_keys:null idx_pid idx_recording_id,idx_channel_name idx_pid
key:null idx_pid null idx_pid
key_len:null 9 null 9
ref:null tb1.pid null mind_work.b.recording_id
rows:105532 2 52766 2第二条语句优化前事19.248s,优化后却变成了24s,很奇怪希望大家指教啊
select * from (select a,* from recordings a,(select recording_id from provisions where channel_name='cmcc') b where a.pid=b.recording_id ) as tb1
,(select distinct pid from recordings where kit like '%aa%') b
where a.pid=b.pid
这条语句却比优化前用时更长,这是什么原因呢