title: Hive Count(DISTINCT column) versus SELECT COUNT(*) from (SELECT DISTINCT column)
body: <p>There have been discussions and claims that the query 2 is faster than query 1.</p>
  
  <blockquote>
    <p>Query 1</p>
    
    <p>SELECT COUNT(DISTINCT A) FROM TAB_X;</p>
    
    <p>QUERY 2</p>
    
    <p>SELECT COUNT(*) FROM (SELECT DISTINCT A FROM TAB_X)</p>
  </blockquote>
  
  <p>I fail to understand exactly why it is so.</p>
  
  <p>This is my understanding of how these queries would be converted to the map reduce behind the scene.</p>
  
  <pre><code>Query 1 
  - Only one stage
  - The mappers emit the Column A as the key and the value as 1. **Is this correct? How distinct is achieved?**
  - There would be only one reducer, which would have to just increment the counter for every key and the list of values that it gets. However, not sure how would that single reducer knows when to emit the final count (**how does it know when to emit eventually?**).
  </code></pre>
  
  <blockquote>
    <p>Query -2
    - Two stages</p>
  
  <pre><code>- Stage 1
       - The mappers emit the key as the column A and the value as 1
       - There will be a lot of reducers, which can aggregate the results for each key and emit the results of that key (which is column A).
  </code></pre>
    
    <blockquote>
      <ul>
      <li>Stage 2
      <ul>
      <li>The mappers gets details of each user and emits the same key for all and value as 1.</li>
      <li>The reducers would just sum these counts and emits the final result.</li>
      </ul></li>
      </ul>
    </blockquote>
  </blockquote>
  
  <p>Can you please help understand/answer my questions inline for query 1 and confirm my understanding of query 2?</p>