Home » Performance » MySQL » subquery costs
| subquery costs [message #1216] |
Sun, 06 May 2007 05:03  |
 kvasnyj
Messages: 1
Registered: May 2007
Location: ru |
Junior Member |
|
|
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:
id select_type table type possible_keys key key_len ref rows Extra
---- ------------ ---------- ------ --------------- -------- ------- -------------------- ---- --------------------------------------------
1 PRIMARY <derived2> 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 <derived4> 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
Can you help me what wrong with this query?
Thank you
|
|
| |
| Re: subquery costs [message #1226 is a reply to message #1216 ] |
Tue, 08 May 2007 14:29  |
|
| kvasnyj wrote on Sun, 06 May 2007 05:03 |
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:
id select_type table type possible_keys key key_len ref rows Extra
---- ------------ ---------- ------ --------------- -------- ------- -------------------- ---- --------------------------------------------
1 PRIMARY <derived2> 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 <derived4> 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
Can you help me what wrong with this query?
Thank you
|
You have 3 tmp tables being created in that query. they're most likely being written to the disk. you should try and optimize those out somehow. Maybe it makes more sense to run queries individually for those tmp tables? dunno. Play with it and see.
|
|
|
Goto Forum:
Current Time: Sun Jul 5 16:11:34 EDT 2009
Total time taken to generate the page: 0.01273 seconds |
Members
Search
Help
Register
Login
Home
