Joining a table that get 500k+ new rows and selecting the newest in a fast query

Question

I have a users table and a calls table there are about 500k new rows inserted into the calls table every day. Each call record has a user_id column and i have about 700 users but what i need to do is select every user and get their most recent call created_at time. What is the fastest way of doing this?

Answer 1

select u.*, MAX(c.created_at)
from users u
    left join calls c on u.user_id = c.user_id
group by u.user_id

The GROUP BY will only work if user_id is the primary key.

Alternative answer:

Use NOT EXISTS to pick only the latest call:

select *
from users u
    left join calls c on u.user_id = c.user_id
where not exists (select 1 from calls c2
                  where c2.created_at> c.created_at
                    and c2.user_id = c.user_id)