CODEWITHSUNDEEP
mysqljoinsubqueryquery-optimization
johncarter
johncarter

Reputation: 71

MySQL slow subquery, should I use JOIN?

I am tracking user events ( log in, log out, page load, etc) that happen on a website.

I want to run a query that gets users that:

In the example below I have created some data that should return just one logged in user with the user_id = 3, because

Assume the time now is 2021-02-24 12:15:00

id user_id description created_at
19954 3 log in 2021-02-24 12:00:00
16085 1 log out 2021-02-24 12:00:00
11844 2 log in 2021-02-24 10:00:00
16850 1 log in 2021-02-24 10:00:00

My current query is this, but it runs really slowly.

SELECT DISTINCT(user_id), id, created_at
FROM events e1
WHERE id = (
            SELECT id
            FROM events e2
            WHERE e2.user_id = e1.user_id
            AND description IN ('log in', 'log out')
            ORDER BY created_at desc
            LIMIT 1
           )
AND description = 'log in'
AND created_at > NOW() - INTERVAL 90 MINUTE
ORDER BY created_at desc

My indexes are as follows.
PRIMARY BTREE TRUE id
description_index BTREE FALSE description
user_desc_created_index BTREE FALSE user_id,description,created_at
user_id_description_index BTREE FALSE user_id,description

I think I might need a join rather than subquery but I'm not sure exactly how. Could anyone lend a hand?

Upvotes: 0

Views: 44

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could try using joi with subqiery for max date group by user_id

SELECT DISTINCT user_id , id, created_at
FROM events e1 
INNER JOIN (
       select user_id, max(created_at) max_date
       from events 
       WHERE  description IN ('log in', 'log out')
       group by user_id 
) t1 
inner join events  t2 ON t1.user_id = t2.user_id and  t1.max_date = t2.created_at
WHERE t2-description = 'log in'
AND t2.created_at > NOW() - INTERVAL 90 MINUTE
ORDER BY t2.created_at desc

Upvotes: 1

Related Questions