database – 在mySQL中优化嵌入式SELECT查询

好的,这是我此刻正在运行的一个查询,它有45,000笔记录,巨细为65MB ……而且即将变得越来越大(以是我也要思量将来的机能)：

SELECT count(payment_id) as signup_count,sum(amount) as signup_amount
FROM payments p
WHERE tm_completed BETWEEN '2009-05-01' AND '2009-05-30'
AND completed > 0
AND tm_completed IS NOT NULL
AND member_id NOT IN (SELECT p2.member_id FROM payments p2 WHERE p2.completed=1 AND p2.tm_completed < '2009-05-01' AND p2.tm_completed IS NOT NULL GROUP BY p2.member_id)

正如你也许想象的那样 – 它会让mysql处事器陷入搁浅……

它的浸染是 – 它只是提取已注册的新用户数目,至少有一个“已完成”付款,tm_completed不为空(由于它仅添补已完成的付款),以及(成员具有的嵌入式选择)从来没有“完成”付款 – 这意味着他是一个新成员(只是由于体系确实从头宣布了等等,这是区分方才被从头招募的现有成员和新成员之间的独一要领第一次).

此刻,是否有任何也许的要领来优化此查询以行使更少的资源或其他对象,并遏制将我的mysql资源放在他们的膝盖上……？

我是否漏掉了任何信息以进一步澄清这一点？让我知道…

编辑：

以下是该表上已有的索引：

PRIMARY PRIMARY 46757 payment_id

member_id INDEX 23378 member_id

payer_id INDEX 11689 payer_id

coupon_id INDEX 1 coupon_id

tm_added INDEX 46757 tm_added,product_id

tm_completed INDEX 46757 tm_completed,product_id

SELECT COUNT(1) AS signup_count,SUM(amount) AS signup_amount
FROM   payments p
WHERE  tm_completed BETWEEN '2009-05-01' AND '2009-05-30'
AND    completed > 0
AND    NOT EXISTS (
           SELECT member_id
           FROM   payments
           WHERE  member_id = p.member_id
           AND    completed = 1
           AND    tm_completed < '2009-05-01');

搜查’tm_completed IS NOT NULL’不是必须的,由于BETWEEN前提体现了这一点.

还要确保你有一个索引：

(tm_completed,completed)

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!