首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >在JOINed表上按组和按顺序排序-复杂和缓慢

问在JOINed表上按组和按顺序排序-复杂和缓慢
EN

Stack Overflow用户
提问于 2013-11-25 11:41:46
回答 1查看 195关注 0票数 4

故事是这样的。我有Users,他们有Children。我希望每天使用CRON作业优惠券发送给在孩子出生日期间隔之间有孩子的用户。我想知道谁将是获得优惠券的用户,以及哪个孩子。另外,我只想给每个孩子寄一张优惠券,孩子必须是用户所拥有的最小的。

我有以下表格

代码语言:javascript
复制
Children
+--------------------------------------+
- Primary Key: childrenID (int)
- Index: userID (int)
- Index: childBirthDate (date)
+--------------------------------------+
- childrenID - userID - childBirthDate -
- 1          - 1      - 21/01/2000     -
- 2          - 1      - 01/11/2013     -
- 3          - 1      - 25/10/2013     -
- 4          - 2      - 01/11/2013     -
- 5          - 3      - 01/11/2013     -
+--------------------------------------+

Users
+------------------------+
- Primary Key: userID (int)
- Index: categoryGroup (varchar)
+------------------------+
- userID - categoryGroup -
- 1      - 'Group1'      -
- 2      - 'Group1'      -
- 3      - 'Group2'      -
- 4      - 'Group2'      -
+------------------------+

CuponRequests
+------------------------+
- Primary Key: ID (int)
- Index: userID (int)
- Index: cuponID (int)
+-----------------------+
- ID - cuponID - userID -
- 1  - 1       - 1      -
- 1  - 2       - 1      -
- 1  - 1       - 2      -
+-----------------------+

这基本上是包含相关列的三个主要表,我有下面的SQL查询来执行和获取我需要的结果。

代码语言:javascript
复制
SELECT users.userID,
       users.categoryGroup children.childBirthDate,
       children.childrenID
FROM users,
  (SELECT *
   FROM
     (SELECT children.childrenID,
             children.childBirthDate,
             users.userID AS child_uid
      FROM children,
           users
      WHERE children.userID = users.userID
      ORDER BY children.childBirthDate DESC)t1
   GROUP BY child_uid)children
WHERE (children.childBirthDate <= DATE_SUB(CURDATE(), INTERVAL 5 MONTH))
  AND (children.childBirthDate > DATE_SUB(CURDATE() , INTERVAL 6 MONTH))
  AND (children.child_uid = users.userID)
  AND ('Group1, Group2' LIKE CONCAT('%', users.categoryGroup, '%'))
  AND NOT EXISTS
    (SELECT userID,
            cuponID
     FROM cuponRequests
     WHERE userID = users.userID
       AND cuponID = 1)
  AND userID = 1
ORDER BY children.childBirthDate DESC

对于这个查询,我只尝试在一个用户和一个优惠券上工作,但是它是自然的行为--这个查询正在处理所有的用户

"cuponID“和间隔来自于脚本的PHP端--我迭代了"cupons”表(这里没有提到),并在每个“优惠券”行上执行这个查询。

问题是,除了在CRON作业环境下运行此脚本外,此查询正在执行约1.5秒钟(O.O),此脚本还在用户注册到网站后立即运行。我有96杯酒--这使注册速度减慢了大约一分钟(这很难)。

我想这个查询

代码语言:javascript
复制
SELECT *
FROM
  (SELECT children.childrenID,
          children.childBirthDate,
          users.userID AS child_uid
   FROM children,
        users
   WHERE children.userID = users.userID
   ORDER BY children.childBirthDate DESC)t1
GROUP BY child_uid

让事情慢下来。我尝试在select查询中执行一个连接,而不是在下面这样的select查询上执行:

代码语言:javascript
复制
FROM users LEFT JOIN children ON children.userID = users.userID

但是,我失去了“childBirthDate DESC的命令”,让这个用户的最小的孩子,我失去了“child_uid组”,只有他的一个孩子。

有什么办法让事情更快,但仍然有效吗?

对不起,我缺乏英语。

编辑:

以下是EXPLAIN SQL的输出

代码语言:javascript
复制
+----+--------------------+---------------+-------+----------------+---------+---------+------------------------------+-------+-----------------------------------------------------+
| id |    select_type     |     table     | type  | possible_keys  |   key   | key_len |             ref              | rows  |                        Extra                        |
+----+--------------------+---------------+-------+----------------+---------+---------+------------------------------+-------+-----------------------------------------------------+
|  1 | PRIMARY            | NULL          | NULL  | NULL           | NULL    | NULL    | NULL                         | NULL  | Impossible WHERE noticed after reading const tables |
|  4 | DEPENDENT SUBQUERY | cuponRequests | ref   | userID,cuponID | userID  | 5       | const                        | 1     | Using where                                         |
|  2 | DERIVED            | <derived3>    | ALL   | NULL           | NULL    | NULL    | NULL                         | 73526 | Using temporary; Using filesort                     |
|  3 | DERIVED            | users         | index | PRIMARY        | PRIMARY | 4       | NULL                         | 69271 | Using index; Using temporary; Using filesort        |
|  3 | DERIVED            | children      | ref   | userID         | userID  | 4       | users.userID                 | 1     |                                                     |
+----+--------------------+---------------+-------+----------------+---------+---------+------------------------------+-------+-----------------------------------------------------+
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2013-11-25 15:03:07

这个查询应该要快得多。我已经转移了关于出生日期的情况。

代码语言:javascript
复制
SELECT *
FROM
  (SELECT children.childrenID,
          children.childBirthDate,
          users.userID AS child_uid
   FROM children,
        users
   WHERE children.userID = users.userID
   AND children.childBirthDate <= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
   AND children.childBirthDate > DATE_SUB(CURDATE() , INTERVAL 6 MONTH)
   ORDER BY children.childBirthDate DESC)t1
GROUP BY child_uid

编辑

我所能写的最快形式的完整查询。我从%中删除了LIKE,将子查询更改为joins,并删除了*。有关出生日期的条件也会被移动。不过,可能会有错误。

代码语言:javascript
复制
SELECT users.userID,
   users.categoryGroup, children.childBirthDate,
   children.childrenID
FROM
  (SELECT MIN(childBirthDate) AS childBirthDate, userID
      FROM children
      WHERE childBirthDate <= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
      AND childBirthDate > DATE_SUB(CURDATE() , INTERVAL 6 MONTH)
      GROUP BY userID) AS ch1
  INNER JOIN users ON users.userID = ch1.userID
  INNER JOIN children ON users.userID = children.userID AND ch1.childBirthDate = children.childBirthDate
  LEFT JOIN CuponRequests ON CuponRequests.userID = userID AND cuponID = 1
  WHERE ('Group1' LIKE users.categoryGroup OR 'Group2' LIKE users.categoryGroup)
  AND CuponRequest.ID IS NULL
  AND userID = 1
ORDER BY children.childBirthDate DESC

长描述

  • 子查询可能比较慢。有时,优化器将无法做正确的事情。使用ON子句编写联接应该更安全。
  • 对于优化器来说,带有GROUP BY的语句甚至更加复杂。它可能有助于在它们中写入附加条件。
  • LIKE '%something%'语句很难使用索引。LIKE 'something%'或LIKE 'something'要快得多。
  • 有时,将*更改为所需参数的显式列表是个好主意。有时,所有需要的信息都在索引中,不需要直接从表中读取。在角落的情况下可能会有一点帮助。
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/20192181

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档