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.

Search Discussions

  • Adam Rich at Feb 10, 2008 at 2:05 am

    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;
  • Scott Marlowe at Feb 10, 2008 at 6:42 am

    On Feb 9, 2008 8:04 PM, Adam Rich 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;

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedFeb 2, '08 at 10:39a
activeFeb 10, '08 at 6:42a
posts3
users3
websitepostgresql.org
irc#postgresql

3 users in discussion

Scott Marlowe: 1 post Rihad: 1 post Adam Rich: 1 post

People

Translate

site design / logo © 2022 Grokbase