MySQL Forums
Forum List  »  Optimizer & Parser

Re: Querying a fast subquery never returns
Posted by: Rick James
Date: May 24, 2012 12:40AM

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
If `date` is of datatype DATE, then
* date(date) may as well be written `date`
* date between '2012-05-20' and '2012-05-21' represents 2 days.

substring(instance_type, 1, 3) <> 'cc1'
can be expressed
instance_type LIKE 'cc1%'

Since you have a "correlated" subquery, that subquery has to be calculated once for every outer query.

Worse than that, I think it will be it will be calculate before the GROUP BY. This _might_ prevent that:
select  `date`, region, 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  instance_type like 'cc1%'
            group by  region ) as ice_count
    from  
      ( SELECT  `date`, region, count(distinct account) as request_count
            from  request_data.v_request_full
            where  date between '2012-05-20' and  '2012-05-21'
              and  zone='ANY'
              and  internal = 0
              and  instance_type like 'cc1%'
            group by  day, region ) rd;

Please provide
SHOW CREATE TABLE v_ice
SHOW CREATE TABLE v_request_full
so that we can critique the datatypes and indexes.
Also, how big are the tables?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Querying a fast subquery never returns
1476
May 24, 2012 12:40AM


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.