The situation: there are users in one table, and their access statistics
in the other. Now I want to find users whose last access time was more
than one month ago. As I've only had to write quite simple queries
involving no sub-selects so far, I'd like to ask your opinion if this
one scales at all or not.
FROM users u
JOIN (SELECT user_id, MAX(stop_time) AS last_use_time
GROUP BY user_id) AS s ON (u.id=s.user_id)
WHERE status='3' AND next_plan_id IS NULL
AND last_use_time < now() - interval '1 month'
ORDER BY last_use_time;
It seems to do the job, but how good is it in the long run? Any way I
could tweak it?