title: Mysql Optimize Query: Trying to Get Average of Subquery
body: <p>I have the following query:</p>
  
  <pre><code>SELECT AVG(time) FROM 
  (SELECT UNIX_TIMESTAMP(max(datelast)) - UNIX_TIMESTAMP(min(datestart)) AS time
      FROM table
      WHERE id IN 
          (SELECT DISTINCT id
              FROM table
              WHERE product_id = 12394 AND datelast &gt; '2011-04-13 00:26:59'
          )
  GROUP BY id
  )
  as T
  </code></pre>
  
  <p>The query gets the greatest datelast value and subtracts it from the greatest datestart value for every ID (which is the length of a user session), and then averages it.</p>
  
  <p>The outer most query is there only to average the resulting times. Is there any way to optimize this query?</p>
  
  <p>Output from EXPLAIN:</p>
  
  <pre><code>id  select_type         table       type            possible_keys           key     key_len ref     rows    extra
  1   PRIMARY             &lt;derived2&gt;  ALL             NULL                    NULL    NULL    NULL    7   
  2   DERIVED             table       index           NULL                    id      16      NULL    26      Using where
  3   DEPENDENT SUBQUERY  table       index_subquery  id,product_id,datelast  id      12      func    2       Using index; Using where
  </code></pre>