# subquery costs
**URL:** https://forums.percona.com/t/subquery-costs/314
**Category:** Other MySQL® Questions
**Created:** [May 6, 2007, 3:33am UTC](https://forums.percona.com/t/subquery-costs/314 "2007-05-06T03:33:34Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@kvasnyj](https://forums.percona.com/u/kvasnyj)
#### Post date: [May 6, 2007, 3:33am UTC](https://forums.percona.com/t/subquery-costs/314/1 "2007-05-06T03:33:34Z")
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 \ ALL NULL NULL NULL NULL NULL
[/I]
Can you help me what wrong with this query?
Thank you
---
### Author:  [@navai](https://forums.percona.com/u/navai)
#### Post date: [May 6, 2007, 11:04am UTC](https://forums.percona.com/t/subquery-costs/314/2 "2007-05-06T11:04:56Z")
i have this problem too :mad:
---
### Author:  [@JGilbert](https://forums.percona.com/u/JGilbert)
#### Post date: [May 8, 2007, 12:59pm UTC](https://forums.percona.com/t/subquery-costs/314/3 "2007-05-08T12:59:04Z")
| [B]kvasnyj wrote on Sun, 06 May 2007 05:03[/B] |
| 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 \ ALL NULL NULL NULL NULL NULL
[/I]
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.