Django admin MySQL slow INNER JOIN

Question

I have a simple model with 3 ForeignKey fields.

class Car(models.Model):
    wheel = models.ForeignKey('Wheel', related_name='wheels')
    created = models.DateTimeField(auto_now_add=True)
    max_speed = models.PositiveSmallIntegerField(null=True)
    dealer = models.ForeignKey('Dealer')
    category = models.ForeignKey('Category')

For the list view in the django admin i get 4 queries. One of them is a SELECT with 3 INNER JOINS. That one query is way to slow. Replacing the INNER JOINs with STRAIGHT_JOIN would fix the issue. Is there a way to patch the admin generated query just before it is evaluated?

Answer 1

I've implemented a fix for INNER JOIN for Django ORM, it will use STRAIGHT_JOIN in case of ordering with INNER JOINs. I talked to Django core-devs and we decided to do this as a separate backend for now. So you can check it out here: https://pypi.python.org/pypi/django-mysql-fix

However, there is one other workaround. Use a snippet from James's answer, but replace select_related with:

qs = qs.select_related('').prefetch_related('wheel', 'dealer', 'category')

It will cancel INNER JOIN and use 4 separate queries: 1 to fetch cars and 3 others with car_id IN (...).

UPDATE: I've found one more workaround. Once you specify null=True in your ForeignKey field, Django will use LEFT OUTER JOINs instead of INNER JOIN. LEFT OUTER JOIN works without performance issues in this case, but you may face other issues that I'm not aware of yet.

Answer 2

You may just specify list_select_related = () to prevent django from using inner join:

class CarAdmin(admin.ModelAdmin):
    list_select_related = ()

Answer 3

You could overwrite

  def changelist_view(self, request, extra_context=None):

method in your admin class inherited from ModelAdmin class

something like this(but this question is rather old): Django Admin: Getting a QuerySet filtered according to GET string, exactly as seen in the change list?

Answer 4

Ok, I found a way to patch the admin generated Query. It is ugly but it seems to work:

class CarChangeList(ChangeList):

    def get_results(self, request):
        """Override to patch ORM generated SQL"""
        super(CarChangeList, self).get_results(request)
        original_qs = self.result_list
        sql = str(original_qs.query)
        new_qs = Car.objects.raw(sql.replace('INNER JOIN', 'STRAIGHT_JOIN'))

        def patch_len(self):
           return original_qs.count()
        new_qs.__class__.__len__ = patch_len

        self.result_list = new_qs


class CarAdmin(admin.ModelAdmin):

    list_display = ('wheel', 'max_speed', 'dealer', 'category', 'created')

    def get_changelist(self, request, **kwargs):
        """Return custom Changelist"""
        return CarChangeList

admin.site.register(Rank, RankAdmin)

Answer 5

I came across the same issue in the Django admin (version 1.4.9) where fairly simple admin listing pages were very slow when backed by MySQL.

In my case it was caused by the ChangeList.get_query_set() method adding an overly-broad global select_related() to the query set if any fields in list_display were many-to-one relationships. For a proper database (cough PostgreSQL cough) this wouldn't be a problem, but it was for MySQL once more than a few joins were triggered this way.

The cleanest solution I found was to replace the global select_related() directive with a more targeted one that only joined tables that were really necessary. This was easy enough to do by calling select_related() with explicit relationship names.

This approach likely ends up swapping in-database joins for multiple follow-up queries, but if MySQL is choking on the large query many small ones may be faster for you.

Here's what I did, more or less:

from django.contrib.admin.views.main import ChangeList


class CarChangeList(ChangeList):

    def get_query_set(self, request):
        """
        Replace a global select_related() directive added by Django in 
        ChangeList.get_query_set() with a more limited one.
        """
        qs = super(CarChangeList, self).get_query_set(request)
        qs = qs.select_related('wheel')  # Don't join on dealer or category
        return qs


class CarAdmin(admin.ModelAdmin):

        def get_changelist(self, request, **kwargs):
            return CarChangeList

Answer 6

I've had slow admin queries on MySQL and found the easiest solution was to add STRAIGHT_JOIN to the query. I figured out a way to add this to a QuerySet rather than being forced to go to .raw(), which won't work with the admin, and have open sourced it as part of django-mysql. You can then just:

def get_queryset(self, request):
    qs = super(MyAdmin, self).get_queryset(request)
    return qs.straight_join()

Answer 7

MySQL still has this problem even in version 8 and Django still doesn't allow you to add STRAIGHT_JOIN in the query set. I found a hackish solution to add STRAIGHT_JOIN...:

This was tested with Django 2.1 and MySQL 5.7 / 8.0

def fixQuerySet(querySet):
    # complete the SQL with params encapsulated in quotes
    sql, params = querySet.query.sql_with_params()
    newParams = ()
    for param in params:
        if not str(param).startswith("'"):
            if isinstance(param, str):
                param = re.sub("'", "\\'", param)
            newParams = newParams + ("'{}'".format(param),)
        else:
            newParams = newParams + (param,)
    rawQuery = sql % newParams

    # escape the percent used in SQL LIKE statements
    rawQuery = re.sub('%', '%%', rawQuery)

    # replace SELECT with SELECT STRAIGHT_JOIN
    rawQuery = rawQuery.replace('SELECT', 'SELECT STRAIGHT_JOIN')

    return querySet.model.objects.raw(rawQuery)

Important: This method returns a raw query set so should be called just before consuming the query set