title: MySQL: "Sending data" vs "Copying to tmp table"
body: <p>I have 2 queries that do the same thing and give the same result set, but writing differently. Looking at their profile, they take almost the same time to execute: around 0.075 seconds</p>
  
  <p>The fascinating thing is that query#1 takes almost all of  the entire process execution time in the state "Copying to tmp table". As query#2 takes almost the entire process execution time in the state "Sending data".</p>
  
  <p>So, my question is:</p>
  
  <p>What state is better for a MySQL query to spent all of its time, "Sending data" or "Copying to tmp table"?</p>
  
  <p>query#1 (spends execution time in the state "Copying to tmp table")</p>
  
  <pre><code>SELECT ac.album_category_id, ac.name, ac2.album_category_id, ac2.name, ac2.description, t2.total
  FROM album_category ac
  JOIN album_category ac2 ON ac2.parent_album_category_id = ac.album_category_id
  JOIN (
      SELECT a.album_category_id cat_id, SUM(result.count_image) total
      FROM album a
      JOIN (
          SELECT IFNULL(a.parent_album_id, a.album_id) p_album_id, COUNT(ai.image_id) count_image
          FROM album_image ai
          JOIN album a ON a.album_id = ai.album_id
          JOIN image i ON i.image_id = ai.image_id
          WHERE i.is_listed = 1
          GROUP BY p_album_id
      ) result ON result.p_album_id = a.album_id
      GROUP BY a.album_category_id
  ) t2 ON t2.cat_id = ac2.album_category_id
  ORDER BY ac.position, ac2.position
  </code></pre>
  
  <p>query#2 (spends execution time in the state "Sending data")</p>
  
  <pre><code>SELECT ac.album_category_id, ac.name, ac2.album_category_id, ac2.name, ac2.description, t3.total
  FROM album_category ac
  JOIN album_category ac2 ON ac2.parent_album_category_id = ac.album_category_id
  JOIN (
      SELECT a2.album_category_id cat_id, SUM(t2.sum_image) total
      FROM album a2
      JOIN (
          SELECT IFNULL(a.parent_album_id, a.album_id) p_album_id, SUM(t1.count_image) sum_image
          FROM album a
          JOIN (
              SELECT ai.album_id album_id, COUNT(ai.image_id) count_image
              FROM album_image ai
              JOIN image i ON i.image_id = ai.image_id
              WHERE i.is_listed = 1
              GROUP BY ai.album_id
          ) t1 ON t1.album_id = a.album_id
          GROUP BY p_album_id
      ) t2 ON t2.p_album_id = a2.album_id
      GROUP BY a2.album_category_id
  ) t3 ON t3.cat_id = ac2.album_category_id
  ORDER BY ac.position, ac2.position
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>As per jkavalik request, i've added the EXPLAIN. I'm not sure how to add it in text, so i've added images instead. Hope that's ok.</p>
  
  <p>EXPLAIN for query #1</p>
  
  <p><a href="https://i.stack.imgur.com/Dj7eb.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Dj7eb.jpg" alt="enter image description here"></a></p>
  
  <p>EXPLAIN for query #2
  <a href="https://i.stack.imgur.com/s5SI7.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/s5SI7.jpg" alt="enter image description here"></a></p>