#16018 closed Bug (duplicate)
Aggregation annotate(Max()) nonoptimal query.
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.3 |
Severity: | Normal | Keywords: | annotate group_by group twice |
Cc: | evgenpioneer@… | Triage Stage: | Unreviewed |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
I've asked this question in django-users and got advice to report.
Here is the point.
Model:
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 it makes:
>>> 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
GROUP BY formed with double main_home
.person_id
. Let's see what happen in this case:
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 | 4604 | Using index; Using temporary | +----+-------------+-----------+-------+---------------+--------------------+---------+------+------+------------------------------+
In "Extra" you can see "Using temporary" option.
Expression execution takes really long time, despite table indexes.
If i'll manually remove main_home
.person_id
doubling from mysql query, "Using temporary" will disappear and execution runs a times fister.
Change History (3)
comment:1 by , 14 years ago
Cc: | added |
---|
comment:2 by , 14 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
comment:3 by , 12 years ago
Component: | ORM aggregation → Database layer (models, ORM) |
---|
Note:
See TracTickets
for help on using tickets.
Duplicate of #15709.