Mysql优化查询:尝试获取子查询的平均值

发布于 2024-11-01 04:17:45 字数 965 浏览 3 评论 0原文

我有以下查询:

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 值(即用户会话的长度)中减去它,然后求平均值。

最外面的查询只是为了平均结果时间。有什么办法可以优化这个查询吗?

解释的输出:

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

前事休说 2024-11-08 04:17:45

第一个 SELECT 真的有必要吗?

SELECT
  AVG(time)
FROM 
(
  SELECT
    UNIX_TIMESTAMP(max(datelast)) - UNIX_TIMESTAMP(min(datestart)) AS time
  FROM
    table
  WHERE
    product_id = 12394 AND datelast > '2011-04-13 00:26:59'
  GROUP BY
    id
)

我现在无法测试,但我认为它也会起作用。否则,您的查询看起来不错。

您可以通过添加 (datelast, Product_id) 键来优化查询(始终将限制性最强的字段放在第一位,以提高选择性)。

Is the first SELECT really necessary ?

SELECT
  AVG(time)
FROM 
(
  SELECT
    UNIX_TIMESTAMP(max(datelast)) - UNIX_TIMESTAMP(min(datestart)) AS time
  FROM
    table
  WHERE
    product_id = 12394 AND datelast > '2011-04-13 00:26:59'
  GROUP BY
    id
)

I can't test now and I think it would work too. Otherwise, your query looks good.

You can optimize the query by adding a (datelast, product_id) key (always put the most restrictive field first, to increase selectivity).

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文