Re: need help optimizing query

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 <rihad(at)mail(dot)ru> 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/