Re: Querying a fast subquery never returns

[Rick James]

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?