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

  • Olexandr Melnyk at Feb 2, 2008 at 11:17 am
    Here's one without a subquery, so may be master:

    select u.login, s.stop_time
    from users as a
    inner join stats as s
    on s.user_id = u.user_id
    where status = '3'
    and next_plan_id is null
    and stop_time < now() - interval '1 month'
    group by u.user_id, u.login, s.stop_time
    order by s.stop_time
    On 2/2/08, rihad wrote:

    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.


    ---------------------------(end of broadcast)---------------------------
    TIP 6: explain analyze is your friend


    --
    Sincerely yours,
    Olexandr Melnyk <><
    http://omelnyk.net/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedFeb 2, '08 at 10:50a
activeFeb 2, '08 at 11:17a
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Rihad: 1 post Olexandr Melnyk: 1 post

People

Translate

site design / logo © 2022 Grokbase