title: Does MySQL optimize subqueries automatically?
body: <p>I wanted to run the following query:</p>
  
  <pre><code>-- Main Query    
  SELECT COUNT(*) FROM table_name WHERE device_id IN 
       (SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA')
  </code></pre>
  
  <p>This following query (sub query from Main Query):</p>
  
  <pre><code>SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA'
  </code></pre>
  
  <p>executes in 7 seconds, giving 2691 rows from a table of 2.1M rows.</p>
  
  <p>I fired the <strong>Main Query</strong> above and it is still executing after 5 mins+ of waiting.</p>
  
  <p>Finally, I executed the sub query separately, took the 2691 records from the result, executed the following query:</p>
  
  <pre><code>-- Main Query (improvised)    
  SELECT COUNT(*) FROM table_name WHERE device_id IN 
       ("device_id_1", "device_id_2", ....., "device_id_2691")
  </code></pre>
  
  <p>Surprisingly, this gave me an answer within 40 seconds.</p>
  
  <p>What gives? Why doesn't MySQL use the same technique that I used and give an answer quickly? Am I doing something wrong?</p>