Querying a fast subquery never returns
Posted by:
Eric Coll
Date: May 22, 2012 05:15PM
We have the following query, let's call it query1, that runs in 14 seconds and returns 8 rows:
select date(date) as day, region, count(distinct account) as request_count,
(select count(distinct account) from test.v_ice
where date between '2012-05-20' and '2012-05-21' and v_ice.region = rd.region and
zone='ANY' and internal = 0 and substring(instance_type, 1, 3) <> 'cc1'
group by region
) as ice_count
from request_data.v_request_full rd
where
date between '2012-05-20' and '2012-05-21' and
zone='ANY' and internal = 0 and substring(instance_type, 1, 3) <> 'cc1'
group by day, region
Now, we use it as a subquery to add a calculated field meaning 8 computations total, but now the query never returns and gets stock in a "sorting" state.
select day, region, request_count, ice_count,
if (ice_count is null, 100.00, ice_count/request_count) as sla
from query1
Where query1 is the query above.
We have repeated the test several times with the same result. There's no locking issues or a busy server involved.
Any explanation for this behavior?