CODEWITHSUNDEEP
mysqlsql
Mike
Mike

Reputation: 311

How to get the rows that some of the users did event1 but did not do event2?

In this query I am trying to find users who visited the page, but did not see the content. All I can find was this query, but this query is slow so much. Is there any way in your mind that I can find a better solution? Thank you!

SELECT COUNT(*) FROM (
  SELECT u.id FROM users u INNER JOIN ( 
    SELECT pl.user_id FROM visited_logs vl 
    LEFT JOIN seen_logs sl ON sl.user_id = vl.user_id 
    WHERE vl.camp_id IN ('33') AND vl.camp_type = 0 
    AND sl.camp_id IS NULL 
  ) as joined ON joined.user_id = u.id 
  WHERE u.status = 'alive'
) derp

Upvotes: 0

Views: 38

Answers (2)

Dmitry Cat
Dmitry Cat

Reputation: 475

i am not sure, but try this solution, hope it helps:

select count(*)
from users u 
join visited_logs vl on vl.user_id = u.id 
     and u.status = 'alive' 
     and vl.camp_id = 33 
     and vl.camp_type = 0 
join seen_logs sl on sl.user_id = vl.user_id
     and sl.camp_id is null

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

This is your query:

SELECT COUNT(*)
FROM (SELECT u.id
      FROM users u INNER JOIN
           (SELECT pl.user_id
            FROM visited_logs vl LEFT JOIN
                 seen_logs sl
                 ON sl.user_id = vl.user_id 
            WHERE vl.camp_id IN ('33') AND vl.camp_type = 0 AND
                  sl.camp_id IS NULL 
           ) joined
           ON joined.user_id = u.id 
      WHERE u.status = 'alive'
     ) derp;

I would suggest writing this:

select count(*)
from users u
where u.status = 'alive' and
      exists (select 1
              from visited_logs vl
              where vl.user_id = u.id and
                    vl.camp_id = 33 and
                    vl.camp_type = 0
             ) and
      not exists (select 1
                  from seen_logs sl
                  where sl.user_id = u.id  -- do you want camp_id here too?
                 );

I think you are missing a comparison to sl.camp_id.

For this query, you want indexes on visited_logs(user_id, camp_type, camp_id) and seen_logs(user_id, camp_id).

Upvotes: 1

Related Questions