#32711: MySQL performance regression in
forms.models.apply_limit_choices_to_to_formfield
----------------------------------------------+------------------------
Reporter: Thomas Achtemichuk | Owner: nobody
Type: Uncategorized | Status: new
Component: Forms | Version: 3.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
----------------------------------------------+------------------------
[https://github.com/django/django/commit/556fa4bbba5ba86bc1646a86fb11ab55405d4aa4
This commit] related to #11707 in Django 3.2 introduced changes to
apply_limit_choices_to_to_formfield. The query generated, for example,
with this field on a model:
{{{
editor = models.ForeignKey(
settings.AUTH_USER_MODEL,
null=True,
blank=True,
limit_choices_to={
'is_staff': True,
'groups__name': 'Editor',
},
on_delete=models.SET_NULL)
}}}
results in the following query which takes ~25s to return on a
`db.r5.xlarge` RDS instance running MySQL 5.7:
{{{
SELECT
`auth_user`.`id`,
`auth_user`.`password`,
`auth_user`.`last_login`,
`auth_user`.`is_superuser`,
`auth_user`.`username`,
`auth_user`.`first_name`,
`auth_user`.`last_name`,
`auth_user`.`email`,
`auth_user`.`is_staff`,
`auth_user`.`is_active`,
`auth_user`.`date_joined`
FROM
`auth_user`
WHERE EXISTS(
SELECT
(1) AS `a`
FROM
`auth_user` U0
INNER JOIN
`auth_user_groups` U1 ON (U0.`id` = U1.`user_id`)
INNER JOIN
`auth_group` U2 ON (U1.`group_id` = U2.`id`)
WHERE (
U2.`name` = 'Editor'
AND U0.`is_staff`
AND U0.`id` = `auth_user`.`id`
)
LIMIT 1
)
ORDER BY `auth_user`.`date_joined` DESC;
...
21 rows in set (26.18 sec)
}}}
Counts for the involved tables:
{{{
mysql> SELECT COUNT(*) FROM auth_user;
+----------+
| COUNT(*) |
+----------+
| 3360439 |
+----------+
mysql> SELECT COUNT(*) FROM auth_group;
+----------+
| COUNT(*) |
+----------+
| 14 |
+----------+
mysql> SELECT COUNT(*) FROM auth_user_groups;
+----------+
| COUNT(*) |
+----------+
| 460 |
+----------+
}}}
And the `EXPLAIN`:
{{{
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: auth_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3201287
filtered: 100.00
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: U2
partitions: NULL
type: const
possible_keys: PRIMARY,name
key: name
key_len: 452
ref: const
rows: 1
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: U1
partitions: NULL
type: eq_ref
possible_keys: user_id,auth_user_groups_403f60f,auth_user_groups_425ae3c4
key: user_id
key_len: 8
ref: bso.auth_user.id,const
rows: 1
filtered: 100.00
Extra: Using index
*************************** 4. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: U0
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: bso.auth_user.id
rows: 1
filtered: 90.00
Extra: Using where
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32711>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.9ee563d2b90e41d8f6e96b8fc77aec98%40djangoproject.com.
