Unnecessary joins in admin changelist query

[Evgeny Sizikov]

Django 1.2.5

Models:

class Client(models.Model):
    name = models.CharField(_('name'), max_length=256)
    name2 = models.CharField(_('unofficial or obsolete name'), max_length=256, blank=True, null=True)
    contact_person = models.CharField(_('contact person'), max_length=256, blank=True, null=True)
    ...

class ClientOffice(models.Model):
    name = models.CharField(_('name'), max_length=256)
    name2 = models.CharField(_('unofficial or obsolete name'), max_length=256, blank=True, null=True)
    ...
    client = models.ForeignKey(Client, verbose_name=_('client'))
    ...

and admin options like these:

class ClientAdmin(admin.ModelAdmin):
    search_fields = ('name', 'name2', 'contact_person', 'clientoffice__name', 'clientoffice__name2')
    ...

Numbers:

>>> Client.objects.count()
10907
>>> ClientOffice.objects.count()
16952

Now, if we try searching for clients in admin by a search query containig several words (>3), got django/admin stalled.

The problem is going to be that each word in the search query leads to additional JOIN in final SQL query beacause of qs = qs.filter(...) pattern. The attached patch is for Django 1.2.5, but adopting for the current SVN trunk is trivial.

[Evgeny Sizikov]

patch has been made from Mercurial repository

[Julien Phalip]

This seems related to #13902 and #15819. Are you able to test if this gets fixed by using Django 1.3 or trunk?

[Aymeric Augustin]

Looking at the code, the part that is modified by the patch is not modified in current trunk. The problematic pattern is still there:

            for bit in self.query.split():
                ...
                qs = qs.filter(...)

[Evgeny Sizikov]

I have checked with Django 1.3 and can now confirm that the problem is still there. Django's ORM in 1.3 still generates SELECT with JOIN's for each qs = qs.filter(...) call, which is going to be made per term in a search query.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[goblinJoel]

This is old, but I think it's a good idea. It's still a problem in Django 1.11, and I didn't see any changes in the 2.x code that looked relevant. It can end up devouring storage space larger than the db itself and suck up CPU to the point where the database system becomes unresponsive or runs out of storage.

I'm not sure whether I should be making unilateral edits, so I suggest renaming to something like:
Multi-word admin searches hang with foreign keys in search_fields

Related:

• ​https://github.com/django/django/pull/7277 with a similar solution
• ​https://groups.google.com/forum/#!topic/django-developers/V8M49P326dI thread related to above PR
• https://code.djangoproject.com/ticket/27864 more recent attempt to mitigate by limiting how many search terms you can use

[Mariusz Felisiak]

#21241 was a duplicate, see for a related discussion.

[Jacob Walls]

​new PR
​mailing list

[Mariusz Felisiak <felisiak.mariusz@…>]

In 948d6d88:

Refs #16063 -- Added tests for searching against multiple related fields in admin changelist.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 76ccce6:

Fixed #16063 -- Adjusted admin changelist searches spanning multi-valued relationships.

This reduces the likelihood of admin searches issuing queries with
excessive joins.