I have a scenario with two tables in MySQL.
Table 1: Users
Table 2: login_history
I'm trying to come up with a query to get those users who were not logged In, in given time period, for example between 2017-09-25 AND 2017-10-2.
I tried to use the sub-query, but that query is quite slow. In the example, I have given dummy data, but actually two tables specially login_history has huge amount of data, thus sub-query is taking time.
It would be something like this:
select u.* from users u where not exists (select 1 from logins l where l.user_id = u.id and l.login_at >= '2017-09-25' and l.login_at <= '2017-10-02' );
If this is slow, then try creating an index on
Assuming you only want users who have logged in at some point, you could all try aggregation:
select l.user_id from logins l group by l.user_id having sum(l.login_at >= '2017-09-25' and l.login_at <= '2017-10-02') = 0;
not exists should be faster.