Slow INNER JOIN in MySQL can be fixed in Django ORM, but should it?

[frol]

There is the bug in MySQL INNER JOIN optimization, but it can be fixed in Django ORM by replacing INNER JOIN with STRAIGHT_JOIN. In short, ordered inner join selections take 3+ seconds, where if replace INNER JOIN with STRAIGHT_JOIN, we get result in 0.001.

Here are steps to reproduce:
1) We need a model with ForeignKey:

from django.conf import settings
from django.db import models

class Child(models.Model):
    name = models.CharField("Name", max_length=255)
    owner = models.ForeignKey(settings.AUTH_USER_MODEL)

2) Initialize data:

from random import choice
from django.contrib.auth import get_user_model
from qq.models import *

User = get_user_model()
users = User.objects.all()
Child.objects.bulk_create(Child(name='child_%d' % i, owner=choice(users)) for i in xrange(400000))

3) Query data with join (the bug appears only if order by is applied):

Child.objects.all().order_by('-id').select_related('owner')[:2]

The resulting query would be:

 {u'sql': u'SELECT `qq_child`.`id`, `qq_child`.`name`, `qq_child`.`other_field`, `qq_child`.`owner_id`, 
`auth_user`.`id`, `auth_user`.`password`, `auth_user`.`last_login`, `auth_user`.`is_superuser`, 
`auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, 
`auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`date_joined` FROM `qq_child` 
INNER JOIN `auth_user` ON ( `qq_child`.`owner_id` = `auth_user`.`id` ) 
ORDER BY `qq_child`.`id` ASC 
LIMIT 2',
  u'time': u'4.608'},

(MySQL caches result until you update the table so the next time it would take 0.001, but not in production if your table updates frequently)

If I replace INNER JOIN with STRAIGHT_JOIN:

list(Child.objects.raw(
    Child.objects.all().order_by('-id').select_related('owner')[:2]\
        .query.sql_with_params()[0].replace('INNER JOIN', 'STRAIGHT_JOIN')
))

I get this result:

 {u'sql': u'SELECT `qq_child`.`id`, `qq_child`.`name`, `qq_child`.`other_field`, `qq_child`.`owner_id`, 
`auth_user`.`id`, `auth_user`.`password`, `auth_user`.`last_login`, `auth_user`.`is_superuser`, 
`auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, 
`auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`date_joined` FROM `qq_child` 
STRAIGHT_JOIN `auth_user` ON ( `qq_child`.`owner_id` = `auth_user`.`id` ) 
ORDER BY `qq_child`.`id` DESC 
LIMIT 2',
  u'time': u'0.001'}] 								

4.6 seconds vs 0.001. Really?

Solutions:
1) SQLAlchemy implemented .with_prefix() method for query: ​http://stackoverflow.com/a/16743228
2) Replace INNER JOIN for MySQL backend with STRAIGHT_JOIN, but it might be not a good idea. I will investigate this today.

Wrong attempts to fix this:
Since select_related is used in django admin change_list if you ask for related fields in columns, devs try to override it with .raw() - ​http://stackoverflow.com/a/15978732/1178806 (I answered in comments why it doesn't work).

[frol]

I've tried completely replace INNER JOIN with STRAIGHT_JOIN and run tests. There were some fails, but only because those tests tried to search 'INNER JOIN' string in queries. Execution time of testing without changes was 561 and after I changed INNER JOIN to STRAIGHT_JOIN, overall execution time was 574. It might be just random fluctuation, but also a regression.

Thus I'm going to create separate project on github with 'enchanced' MySQL backend and ask people to try it.

[frol]

I made a workaround for SQLCompiler and pushed it to GitHub: ​https://github.com/frol/django-mysql-fix
Good news: It works! It passes all Django-test without changes in Django.
Bad news: It looks awful because it manipulates with JoinInfo objects and tables order.

It has to be tested in real projects...

[Tim Graham]

I'm not sure, but I found ​http://stackoverflow.com/a/516720 which indicates this change may not be a good idea. Could you reopen the ticket if you have information suggesting otherwise? If you still think the change is a good one, it would probably be a good topic to bring up on the django-developers mailing list.

[frol]

I've found another workaround. Once you specify null=True to the 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.

@timo I've seen that comment, but, since Django tests passed and I released the project for everybody, I've expected to hear real complains.