title: understanding perf of mysql query using explain extended
body: <p>I am trying to understand performance of an SQL query using MySQL.
  With only indexes on the PK, the query failed to complete in over 10mins.
  I have added indexes on all the columns used in the where clauses (timestamp, hostname, path, type) and the query now completes in approx 50seconds -- however this still seems a long time for what does not seem an overly complex query.</p>
  
  <p>So, I'd like to understand what it is about the query that is causing this. My assumption is that my inner subquery is in someway causing an explosion in the number of comparisons necessary.</p>
  
  <p>There are two tables involved:</p>
  
  <p><strong>storage</strong> (~5,000 rows / 4.6MB ) and <strong>machines</strong> (12 rows, &lt;4k)</p>
  
  <p>The query is as follows:</p>
  
  <pre><code>SELECT T.hostname, T.path, T.used_pct, 
        T.used_gb, T.avail_gb, T.timestamp, machines.type AS type
        FROM storage AS T
        JOIN machines ON T.hostname = machines.hostname
        WHERE timestamp = ( SELECT max(timestamp) FROM storage AS st
                              WHERE st.hostname = T.hostname AND
                                                st.path = T.path)
        AND (machines.type = 'nfs')
        ORDER BY used_pct DESC
  </code></pre>
  
  <p>An EXPLAIN EXTENDED for the query returns the following:</p>
  
  <pre><code>id       select_type        table     type       possible_keys        key          key_len    ref                            rows     filtered      Extra
  1        PRIMARY            machines  ref        hostname,type        type         768        const                          1        100.00        Using where; Using temporary; Using filesort
  1        PRIMARY            T         ref        fk_hostname          fk_hostname  768        monitoring.machines.hostname   4535     100.00        Using where
  2        DEPENDENT SUBQUERY st        ref        fk_hostname,path     path         1002       monitoring.T.path              648      100.00        Using where
  </code></pre>
  
  <p>Noticing that the 'extra' column for Row 1 includes 'using filesort' and question:
  <a href="https://stackoverflow.com/questions/7999833/mysql-explain-query-understanding">MySQL explain Query understanding</a>
  states that "Using filesort is a sorting algorithm where MySQL isn't able to use an index for sorting and therefore can't do the complete sort in memory."</p>
  
  <p>What is the nature of this query which is causing slow performance?</p>
  
  <p>Why is it necessary for MySQL to use 'filesort' for this query?</p>