Mysql优化查询：尝试获取子查询的平均值(Mysql Optimize Query: Trying to Get Average of Subquery)

 我有以下查询： 
 
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 > '2011-04-13 00:26:59'
        )
GROUP BY id
)
as T
 
 查询获取最大的datelast值，并从每个ID（即用户会话的长度）的最大datestart值中减去该值，然后对其进行平均。 
 
 最外面的查询仅用于平均结果时间。 有没有办法优化这个查询？ 
 
 来自EXPLAIN的输出： 
 
id  select_type         table       type            possible_keys           key     key_len ref     rows    extra
1   PRIMARY             <derived2>  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

I have the following query:
 
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 > '2011-04-13 00:26:59'
        )
GROUP BY id
)
as T
 
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.
 
The outer most query is there only to average the resulting times. Is there any way to optimize this query?
 
Output from EXPLAIN:
 
id  select_type         table       type            possible_keys           key     key_len ref     rows    extra
1   PRIMARY             <derived2>  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

原文：https://stackoverflow.com/questions/5647857