Ok, here goes:
First, some background - UserProfile is the
settings.AUTH_PROFILE_MODULE and the Application model has a
ForeignKey to User. There's ~90,000 users/userprofiles, ~110,000
applications (most have only one application, some have 2). The
database is mysql. If that's not enough info, let me know and I'll see
if I can put together a simple reproduce-able test case.
The queryset giving the problem is
UserProfile.objects.values(
'ird_number', 'user__first_name', 'user__last_name', 'user__pk',
).annotate(
Max('user__application__creation_date'),
).order_by('-user__application__creation_date__max')
I'm beginning to think it might be a bug - because UserProfile and
User are joined by a one-to-one relationship, it's sufficient to group
by one of their primary keys, and I think the ORM should know this.
Should I open a ticket for this?
Greg
On 15 February 2010 13:07, Russell Keith-Magee <freakboy3...@gmail.com> wrote:
> On Mon, Feb 15, 2010 at 6:30 AM, Greg <gregplaysgui...@gmail.com> wrote:
>> I have a large-ish query which is taking ~10 seconds to run, but I can
>> get it down to less than a second by changing the group by part of the
>> query from
>>
>> GROUP BY `refunds_userprofile`.`ird_number` ,
>> `auth_user`.`first_name` , `auth_user`.`last_name` ,
>> `refunds_userprofile`.`user_id` , `auth_user`.`id` ,
>> `auth_user`.`username` , `auth_user`.`first_name` ,
>> `auth_user`.`last_name` , `auth_user`.`email` ,
>> `auth_user`.`password` , `auth_user`.`is_staff` ,
>> `auth_user`.`is_active` , `auth_user`.`is_superuser` ,
>> `auth_user`.`last_login` , `auth_user`.`date_joined`
>>
>> to
>>
>> GROUP BY `refunds_userprofile`.`ird_number`
>>
>> (note this doesn't change the query results)
>>
>> I figured I might have to use some undocumented methods to do this,
>> but I couldn't figure it out - I've tried explicitly setting the
>> query.group_by attribute with no success. Does anyone know how I could
>> do this?
>
> The short answer is "you don't". Django doesn't expose any way to
> manually control GROUP BY because that is a relational construct, and
> Django is attempting to provide an Object-like wrapper.
>
> As for why dropping GROUP BY clauses is faster - remember, t's always
> possible to get the wrong answer in O(1) time :-) Daniel has asked the
> more important question - what are you trying to do? Django is
> generally pretty careful about adding GROUP BY clauses; arbitrarily
> dropping them make me a little nervous. It's possible you're found a
> bug, but it's impossible to confirm that without more details.
>
> Yours
> Russ Magee %-)
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-us...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
>
--
http://gregbrown.co.nz/
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
