Thread: help optimizing query
Hi all, 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. SELECT u.login,last_use_time FROM users u JOIN (SELECT user_id, MAX(stop_time) AS last_use_time FROM stats 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? Thanks.
> It seems to do the job, but how good is it in the long run? Any way I > could tweak it? I think this form will work the best: SELECT u.login, MAX(s.stop_time) AS last_use_time FROM users u, stats s WHERE u.id=s.user_id AND u.status='3' AND u.next_plan_id IS NULL GROUP BY u.login HAVING MAX(s.stop_time) < (now() - interval '1 month') ORDER BY last_use_time;
On Feb 9, 2008 8:04 PM, Adam Rich <adam.r@sbcglobal.net> wrote: > > It seems to do the job, but how good is it in the long run? Any way I > > could tweak it? > > > I think this form will work the best: > > > SELECT u.login, MAX(s.stop_time) AS last_use_time > FROM users u, stats s > WHERE u.id=s.user_id > AND u.status='3' AND u.next_plan_id IS NULL If only ba small number of fields have next_plan as null, an they correlate to the status normally, then an index on state where next_plan_id is null might help here. > GROUP BY u.login > HAVING MAX(s.stop_time) < (now() - interval '1 month') > ORDER BY last_use_time;