On Fri, May 6, 2011 at 1:26 AM, Satan Study Django
<satan.come.and.kill.your.family.an...@gmail.com> wrote:
> Hi. I've some question about aggregation annotate functions, like Max.
> I don't realy like the queries it makes. Let me explain.
>
> Models:
> class Person (models.Model):
> name = models.CharField (max_length = 100)
> login = models.CharField (max_length = 30)
> class Home (models.Model):
> person = models.ForeignKey (Person)
> state = models.ForeignKey (States)
> date = models.DateTimeField ()
> host = models.ForeignKey (Hosts)
> time_spent = models.PositiveIntegerField (null = True)
>
>
> Here is the expression with query made by it:
>
>>>> print Home.objects.values('person').annotate(Max('id')).order_by().query
> SELECT `main_home`.`person_id`, MAX(`main_home`.`id`) AS `id__max`
> FROM `main_home`
> GROUP BY `main_home`.`person_id`, `main_home`.`person_id`
> ORDER BY NULL
>
> Here i try to explain it:
>
> mysql> explain SELECT `main_home`.`person_id`, MAX(`main_home`.`id`)
> AS `id__max`
> FROM `main_home`
> GROUP BY `main_home`.`person_id`, `main_home`.`person_id`
> ORDER BY NULL;
> +----+-------------+-----------+-------+---------------
> +--------------------+---------+------+------
> +------------------------------+
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +----+-------------+-----------+-------+---------------
> +--------------------+---------+------+------
> +------------------------------+
> | 1 | SIMPLE | main_home | index | NULL | main_home_21b911c5 | 4 |
> NULL | 2188 | Using index; Using temporary |
> +----+-------------+-----------+-------+---------------
> +--------------------+---------+------+------
> +------------------------------+
>
> As you can see, Using temporary is added, it's bacause
> `main_home`.`person_id` used in GROUP BY 2 times:
> GROUP BY `main_home`.`person_id`, `main_home`.`person_id`
> and it makes query to run really long time.
>
> If i'll manually remove one of `main_home`.`person_id` from GROUP BY,
> query explain will contain no Using temporary, so execution takes
> short time.
>
> Could somebody explain to me - is it a feature? If it is, i really
> want to listen reason why. If not - then what? A bug?
Looks like a bug to me. If there isn't already a ticket describing
this, please open one so that the problem isn't forgotten.
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-users@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.
