Buyers structure by registration date query optimisation

Question

I would like to show buyers structure by their registration date e.g.:

H12016 10.000 buyers

from which

2.000 registered in H12014

4.000 registered in H22014

etc.

I have two queries for that:

Number 1 (buyers from H12016 (about 50k records)):

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

Number 2 (users_ids and their registration (insert) date (about 3,8M users)):

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

FROM
mwids m

WHERE
m.`type` = 'insert'

Both queries separately run fine, but when I try to combine them like so:

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

this query runs "indefinetely" and I have to kill it after some time.

I do not belive it should run that long. If the query Number 2 was smaller i.e. about 1M users I could combine results in Excel in matter of seconds. So why is it not possible inside the database? What am I doing wrong?

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

Try with INNER JOIN.