Hello,
Next query execute 5 sec and I cant understand why:
select sum(f.y)count()tab.w as ord, tab.w as w, tab.id_dict
from refren f,
(select 1 as w,rf.id_src, rf.id_dict
from refren f, refren_ f1, relref1 rf
where f1.id=rf.id_dict and rf.id_src=f.id and f.type=1 and
f.y>0.1 and f1.ref=11271768
group by rf.id_src, rf.id_dict
having sum(f.y)>=0.3 and count()>0
union
select avg(r2.w) as w,r1.id_src, r1.id_dict
from relref1 r2,refren_ f1, refren f2, l2l1 r1
where r2.id_src=r1.id_dict and r2.id_dict=f1.id and
f2.id=r1.id_src and f1.ref=11271768 and f2.y>0.1
group by r1.id_src, r1.id_dict having sum(f2.y)>0.5
and count(*)>0) as tab
where tab.id_src=f.id and f.y>0.1
group by tab.id_dict order by ord desc, id_dict limit 50
l2l1 is a view
select sum(f.y)count()*sum(r.w) as ord, r.id_src,r.id_dict from relref1 r,refren f
where r.id_src=f.id and f.y>0.1 and f.id=r.id_src
group by r.id_src,r.id_dict
order by ord desc, r.id limit 100
and explain:
[I]id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY system NULL NULL NULL NULL 1
1 PRIMARY f const PRIMARY,id PRIMARY 8 const 1
2 DERIVED f range PRIMARY,id,type type 5 NULL 9 Using where; Using temporary; Using filesort
2 DERIVED f1 ref PRIMARY,id,ref ref 8 4
2 DERIVED rf ref src-dict,dict src-dict 16 kms.f.id,kms.f1.id 2 Using index
3 UNION f1 ref PRIMARY,id,ref ref 8 4 Using temporary; Using filesort
3 UNION ALL NULL NULL NULL NULL 200
3 UNION f2 eq_ref PRIMARY,id PRIMARY 8 r1.id_src 1 Using where
3 UNION r2 ref src-dict,dict src-dict 16 r1.id_dict,kms.f1.id 2
4 DERIVED f ALL PRIMARY,id NULL NULL NULL 56 Using where; Using temporary; Using filesort
4 DERIVED r ref src-dict src-dict 8 kms.f.id 55
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
[/I]
Can you help me what wrong with this query?
Thank you