Opened 14 months ago

Closed 13 months ago

Last modified 12 months ago

#22438 closed Uncategorized (wontfix)

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

Reported by: frol Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: mysql, orm, slow query, join
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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).

Change History (4)

comment:1 Changed 14 months ago by frol

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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.

comment:2 Changed 13 months ago by 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...

comment:3 Changed 13 months ago by timo

  • Resolution set to wontfix
  • Status changed from new to closed

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.

comment:4 Changed 12 months ago by 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.

Note: See TracTickets for help on using tickets.
Back to Top