Re: Slow-ish Query Needs Some Love

On Feb 2, 6:06 am, Edgardo Portal <egportal2(dot)(dot)(dot)(at)yahoo(dot)com> wrote:
> On 2010-02-02, Matt White <mattw(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>
> > I have a relatively straightforward query that by itself isn't that
> > slow, but we have to run it up to 40 times on one webpage load, so it
> > needs to run much faster than it does. Here it is:
>
> > SELECT COUNT(*) FROM users, user_groups
> >  WHERE users.user_group_id = user_groups.id AND NOT users.deleted AND
> > user_groups.partner_id IN
> >  (partner_id_1, partner_id_2);
>
> > The structure is partners have user groups which have users. In the
> > test data there are over 200,000 user groups and users but only ~3000
> > partners. Anyone have any bright ideas on how to speed this query up?
>
> Can you avoid running it 40 times, maybe by restructuring the
> query (or making a view) along the lines of the following and
> adding some logic to your page?
>
> SELECT p.partner_id, ug.user_group_id, u.id, count(*)
>   FROM partners p
>        LEFT JOIN user_groups ug
>               ON ug.partner_id=p.partner_id
>        LEFT JOIN users u
>               ON u.user_group_id=ug.id
>  WHERE NOT u.deleted
>  GROUP BY 1,2,3
> ;

Thanks for the suggestion. The view didn't seem to speed things up.
Perhaps we can reduce the number of times it's called, we'll see. Any
additional ideas would be helpful. Thanks.