id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32711	MySQL performance regression in forms.models.apply_limit_choices_to_to_formfield	Thomas Achtemichuk	nobody	"[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
}}}"	Uncategorized	closed	Forms	3.2	Normal	worksforme		Adam Johnson	Unreviewed	0	0	0	0	0	0