optimize django admin sql

Question

I have the following database structure:

class Book(models.Model):
    id = models.AutoField(primary_key=True, db_column='id')
    name = models.CharField(max_length=255, db_column='name')
    author = models.ForeignKey('Author', to_field='id', db_column='author_id')


class Author(models.Model):
    id = models.AutoField(primary_key=True, db_column='id')
    fio = models.CharField(max_length=255, db_column='fio')

    def __unicode__(self):
        return self.name

And this Admin class for Book:

class BookAdmin(admin.ModelAdmin):
    list_display = ('id', 'name',)
    fields = ('id', 'name', 'author',)
    readonly_fields = ('id',)
    raw_id_fields = ('author',)

Everything works fine when It's 500-1000 book records, but on 1-2 million books page freeze for few minutes before shows something. Profiler show me that django join books with authors and then cut last 100 records.

SELECT ••• FROM `books` INNER JOIN `authors` ON ( `books`.`author_id` = `authors`.`id` ) ORDER BY `books`.`id` DESC LIMIT 100

How can I optimize django to join authors after select books from database? Or use something like that

select * from (SELECT * FROM books ORDER BY books.id DESC LIMIT 100) t, authors a where t.author_id = a.id

Answer 1

You are experiencing a known issue: Slow INNER JOIN in MySQL can be fixed in Django ORM, but should it?

There is a project called django-mysql-fix that was started on PyCon 2014 sprints that introduced a custom mysql database backend for fixing INNER JOIN issues specifically:

This project contains optimizations (hacks) for MySQL for Django ORM.

There are two very simple ways to cacth INNER JOIN bug:

• Once you specify field from foreign table in list_display in your Django Admin model;

• Once you try to sort (order) by field from foreign table.

As far as I understand, under the hood it replaces all INNER JOINs with STRAIGHT_JOINs, quote from mysql docs:

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

Also see:

• Django admin MySQL slow INNER JOIN
• When to use STRAIGHT_JOIN with MySQL