MySQL Forums
Forum List  »  Optimizer & Parser

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Querying a fast subquery never returns
2224
May 22, 2012 05:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.