[Django] #34639: MySQL 8.0 hand indefinitely when using the admin search with a Foreign Key and Annotate

[Django]

#34639: MySQL 8.0 hand indefinitely when using the admin search with a Foreign Key
and Annotate
------------------------------------------+------------------------
Reporter: Nicolas Lupien | Owner: nobody
Type: Bug | Status: new
Component: contrib.admin | Version: 4.2
Severity: Normal | Keywords: mysql
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------+------------------------
We've moved to MySQL 8.0 in order to use Django 4.2 but our production
system went down and we reverted to using MySQL 5.7 with Django 4.1. We've
currently found a workaround that I'll add at the end of the bug report.

If we use the search function of the admin on model with a foreign key and
we override ModelAdmin.get_queryset with annotate, the search freezes our
database. It had the same effect on Google Cloud SQL and on a local docker
image of MySQL 8.0 and it works fine on both environment when using MySQL
5.7.

The code:

models.py

{{{
class Organization(models.Model):
name = models.CharField(max_length=255)

class Member(models.Model):
name = models.CharField(max_length=255)
organization = models.ForeignKey(Organization,
on_delete=models.CASCADE, null=True)
}}}

admin.py

{{{
class OrganizationAdmin(admin.ModelAdmin):
search_fields = ["name", "member__name"]
list_display = ["name", "member_count"]

class Meta:
model = models.Organization

def get_queryset(self, request):
return super().get_queryset(request).annotate(Count("member"))

def member_count(self, instance):
return instance.member__count
}}}

I found that the ChangeList applies the override to get_queryset
containing the annotate multiple times making the query extremely
expensive. Give only 500 members it goes through 125,000,000 (500 * 500 *
500) rows.

The workaround: If we override the ChangeList queryset, the call to
annotate happens only once and the query is fine.

{{{
class CustomChangeList(ChangeList):
def get_queryset(self, request):
return
super().get_queryset(request).annotate(Count("locker_connectors"))

class OrganizationAdmin(admin.ModelAdmin):
search_fields = ["name", "member__name"]
list_display = ["name", "member_count"]

class Meta:
model = models.Organization

def get_queryset(self, request):
return super().get_queryset(request).annotate(Count("member"))

def member_count(self, instance):
return instance.member__count

def get_changelist(self, request, **kwargs):
return CustomChangeList
}}}

I created a repo with more details and the complete steps to reproduce the
issue: https://github.com/betaflag/django-sqlbugdemo

--
Ticket URL: <https://code.djangoproject.com/ticket/34639>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.