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 = ()