故事是这样的。我有Users,他们有Children。我希望每天使用CRON作业优惠券发送给在孩子出生日期间隔之间有孩子的用户。我想知道谁将是获得优惠券的用户,以及哪个孩子。另外,我只想给每个孩子寄一张优惠券,孩子必须是用户所拥有的最小的。
我有以下表格
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查询来执行和获取我需要的结果。
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杯酒--这使注册速度减慢了大约一分钟(这很难)。
我想这个查询
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查询上执行:
FROM users LEFT JOIN children ON children.userID = users.userID但是,我失去了“childBirthDate DESC的命令”,让这个用户的最小的孩子,我失去了“child_uid组”,只有他的一个孩子。
有什么办法让事情更快,但仍然有效吗?
对不起,我缺乏英语。
编辑:
以下是EXPLAIN SQL的输出
+----+--------------------+---------------+-------+----------------+---------+---------+------------------------------+-------+-----------------------------------------------------+
| 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 | |
+----+--------------------+---------------+-------+----------------+---------+---------+------------------------------+-------+-----------------------------------------------------+发布于 2013-11-25 15:03:07
这个查询应该要快得多。我已经转移了关于出生日期的情况。
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,并删除了*。有关出生日期的条件也会被移动。不过,可能会有错误。
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'要快得多。*更改为所需参数的显式列表是个好主意。有时,所有需要的信息都在索引中,不需要直接从表中读取。在角落的情况下可能会有一点帮助。https://stackoverflow.com/questions/20192181
复制相似问题