most efficient way of finding reengaged users

Question

i want to get all active users in the past 24 hours, and see whether they've interacted with us before. i have the following query:

SELECT $                                                                                                                                                                             
    DISTINCT user_id $                                                                                                                                                               
FROM $                                                                                                                                                                               
    activity $                                                                                                                                                                
WHERE user_id in (SELECT $                                                                                                                                                           
                      DISTINCT user_id $                                                                                                                                             
                  FROM $                                                                                                                                                             
                      activity $                                                                                                                                              
                  WHERE $                                                                                                                                                            
                      date > DATE_SUB(NOW(), INTERVAL 24 HOUR)) AND $                                                                                                                
      date < DATE_SUB(NOW(), INTERVAL 24 HOUR);

i have indexes on user_id and date. can this be made more efficient?

All most same statement is running inside user_id in! why don't you try just that statement which you have put inside it. Use Explain — Raul, Dec 12 '13 at 07:17
i did, it was going through too many rows. this is why i came to SO — tipu, Dec 12 '13 at 07:39

tipu · Answer 1 · 2013-12-13T17:57:59.470

chris put me in the right track when he instructed me to use EXISTS. the correct query is:

SELECT $                                                                                                                                                                                 DISTINCT user_id $                                                                                                                                                               FROM $                                                                                                                                                                               
    activity o $                                                                                                                                                              
WHERE $                                                                                                                                                                              
    EXISTS (SELECT $                                                                                                                                                                 
                1 $                                                                                                                                                                  
            FROM $                                                                                                                                                                   
                activity i $                                                                                                                                                  
            WHERE $                                                                                                                                                                  
                o.user_id = i.user_id AND $                                                                                                                                          
                DATEDIFF(o.date, i.date) > 0) AND $                                                                                                                                                           
    date > DATE_SUB(NOW(), INTERVAL 24 HOUR)$

explain mentioned rows examined went from 20k to 170

hello, `LIMIT 1` is not required. MySQL automatically break sub-query when first row matches when `EXISTS` is used. — Jason Heo, Dec 12 '13 at 08:17
Thanks. Looks better! BTW, I wonder if you test two queries in my answer which is faster? would you mind? — Jason Heo, Dec 12 '13 at 08:23

Jason Heo · Answer 2 · 2013-12-13T01:39:15.310

0

could you try this?

SELECT DISTINCT user_id
FROM activity INNER JOIN
    (
        SELECT DISTINCT user_id
        FROM activity
        WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
            AND date < DATE_SUB(NOW(), INTERVAL 24 HOUR
    ) x ON x.user_id = activity.user_id

Or

SELECT DISTINCT user_id
FROM activity a1
WHERE EXISTS (
    SELECT 1
    FROM activity a2
    WHERE a2.user_id = a1.user_id
      date > DATE_SUB(NOW(), INTERVAL 24 HOUR))
      AND date < DATE_SUB(NOW(), INTERVAL 24 HOUR
);

edited Dec 13 '13 at 01:39

answered Dec 12 '13 at 08:15

Jason Heo

9,956
2
36
64

2nd query: | 1 | PRIMARY | a1 | index | 0 | activity_fbfc09f1 | 4 | 0 | 29801 | Using where | – tipu Dec 13 '13 at 01:35
first query has confusing paranthesis after INTERVAL 24 HOUR)) which breaks query – tipu Dec 13 '13 at 01:36
@tipu Oops. Thanks. have updated. `HOUR))` to `HOUR)` – Jason Heo Dec 13 '13 at 01:39

most efficient way of finding reengaged users

2 Answers2