MySQL slow subquery, should I use JOIN?

Question

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:

• are logged in
• haven't logged out
• didn't log in more than 90 minutes ago

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

• user_id 1 has logged out
• user_id 2 timed out
• user_id 3 logged in and hasn't timed out or manually logged out

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?

Answer 1

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