title: Fast to query slow to create table
body: <p><a href="https://i.stack.imgur.com/bwGhH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bwGhH.png" alt="sample data"></a></p>
  
  <p>I have an issue on creating tables by using select keyword (it runs so slow). The query is to take only the details of the animal with the latest entry date. that query will be used to inner join another query.</p>
  
  <pre><code>SELECT *
  FROM amusementPart a 
  INNER JOIN (
      SELECT DISTINCT name, type, cageID, dateOfEntry 
      FROM bigRegistrations
      GROUP BY cageID
  ) r ON a.type = r.cageID
  </code></pre>
  
  <p>But because of slow performance, someone suggested me steps to improve the performance. 1) use temporary table,  2)store the result and use it and join it the the other statement.</p>
  
  <pre><code>   use myzoo 
     CREATE TABLE animalRegistrations AS 
     SELECT DISTINCT name, type, cageID, MAX(dateOfEntry) as entryDate 
     FROM bigRegistrations
     GROUP BY cageID
  </code></pre>
  
  <p>unfortunately, It is still slow. If I only use the select statement, the result will be shown in 1-2 seconds. But if I add the create table, the query will take ages (approx 25 minutes)</p>
  
  <p>Any good approach to improve the query time?</p>
  
  <p>edit: the size of big registration table is around 3.5 million rows</p>