关联查询优化

[lbd8848]

有表 a
bh ..... hm1 hm2 zt.....
=======================
1 111 000 0
2 111 333 0
3 222 111 1
4 555 111 0
5 111 000 0
...
bh为主键,hm1,hm2 普通索引,zt作bitmap索引

有表b ,b是a的子表
bh zd1 zd2
====================
1 mmm nnn
2 mmm1 333
3 323 232
4 3ss 3232
5 333 432

bh普通索引

现在要得到所有a表和b表关联的所有hm1 or hm2 反包含于字符串'111,3332,'并且zt=0中的所有记录
现在所写的查询是
select * from a inner join b on a.bh=b.bh where a.zt=0 and (instr('111,3332,',a.hm1)>0 or instr('111,3332,',a.hm2)>0)
但查询速度不快.
不知如何优化,期待中.........

[sleepzzzzz]

1.创建函数索引
create index idx_1 on a(instr('111,3332,',hm1));
create index idx_1 on a(instr('111,3332,',hm2));

2.
select * from b join (select * from a where zt=0)t on b.bh=t.bh
where instr('111,3332,',t.hm1)>0 or instr('111,3332,',t.hm2)>0


[Quote=引用楼主 lbd8848 的帖子:]
有表 a
bh ..... hm1 hm2 zt.....
=======================
1 111 000 0
2 111 333 0
3 222 111 1
4 555 111 0
5 111 000 0
...
bh为主键,hm1,hm2 普通索引,zt作bitmap索引

有表b ,b是a的子表
bh zd1 zd2
====================
1 mmm nnn
2 mmm1 333
3 323 232
4 3ss 3232
5 …
[/Quote]

[sleepzzzzz]

应该这样：

1.创建函数索引
create index idx_1 on a(instr('111,3332,',hm1));
create index idx_2 on a(instr('111,3332,',hm2));

2.ＳＱＬ
select * from b join (select * from a where zt=0 and (instr('111,3332,',hm1)>0 or instr('111,3332,',hm2)>0))t on b.bh=t.bh

[白发程序猿]

函数索引或

select * 
from a inner join b on a.bh=b.bh 
where a.zt=0 and (a.hm1='111' or a.hm1='3332' or a.hm2='111' or a.hm2='3332')

[lbd8848]

谢谢确实有所提高,结贴