mysql - 买家结构按注册日期查询优化

Question

我想按注册日期显示买家结构，例如:

H12016 10.000 buyers

来自哪里

2.000 registered in H12014

4.000 registered in H22014

等等

我有两个问题:

第 1 名(H12016 的买家(约 50k 条记录)):

SELECT DISTINCT
r.idUsera as id_usera

FROM
rezerwacje r

WHERE
r.dataZalozenia between '2016-01-01' and '2016-07-01'
and r.`status` = 'zabookowana'

ORDER BY
id_usera

数字 2(用户 ID 及其注册(插入)日期(约 380 万用户)):

SELECT
m.user_id,
date(m.action_date) as data_insert

FROM
mwids m

WHERE
m.`type` = 'insert'

这两个查询分别运行良好，但是当我尝试像这样组合它们时:

SELECT DISTINCT
r.idUsera as id_usera,
t1.data_insert

FROM
rezerwacje r

LEFT JOIN
    (
    SELECT
    m.user_id,
    date(m.action_date) as data_insert

    FROM
    mwids m

    WHERE
    m.`type` = 'insert'
    ) t1 ON t1.user_id = r.idUsera

WHERE
r.dataZalozenia between '2016-01-01' and '2016-07-01'
and r.`status` = 'zabookowana'

ORDER BY
id_usera

此查询“不确定地”运行，一段时间后我必须终止它。

我不相信它应该运行那么久。如果查询编号 2 较小，即大约 100 万用户，我可以在几秒钟内将结果合并到 Excel 中。那么为什么在数据库内部是不可能的呢？我做错了什么？

Answer 1

SELECT DISTINCT
r.idUsera as id_usera,
t1.data_insert

FROM
rezerwacje r

INNER JOIN
    (
    SELECT
    m.user_id,
    date(m.action_date) as data_insert

    FROM
    mwids m

    WHERE
    m.`type` = 'insert'
    ) t1 ON t1.user_id = r.idUsera

WHERE
r.dataZalozenia between '2016-01-01' and '2016-07-01'
and r.`status` = 'zabookowana'

ORDER BY
id_usera

尝试使用 INNER JOIN。