MySQL 8.0 hangs indefinitely when using the admin search with a Foreign Key and Annotate

[Nicolas Lupien]

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 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