Django ORM: replace INNER JOIN with STRAIGHT_JOIN

Similar to this post here from 9+ years ago: #22438 (Slow INNER JOIN in MySQL can be fixed in Django ORM, but should it?) – Django

We have queries being generated that use “inner join” - which should be fine, but the SQL engine optimiser is not playing nice with it.
When I swap “inner join” for “straight_join” - thus by-passing the optimiser - I get the performance I’d expect.
With “inner join” - 1+ hour
With “straight_join” - 2 seconds.

This isnt a fault of Django, but after a lot of researching and tinkering, it appears sometimes you have to by-pass the optimiser to get the exact query you want.

Is this possible to do in Django? Perhaps Ive missed it in the docs. Even if its something a bit hacky like:

qs = myModel.object.filters(…).otherStuff(…)
qs.raw_sql.replace("inner ", “straight_”)

that would be enough for me to test this further.

Any pointers would be greatly appreciated.

If you really wanted to go that far, you could just write a raw query for those cases where it’s necessary.

Django doesn’t allow you to specify the join type as it follows this particular (simplified) logic

  • If the relationship is nullable use a LEFT JOIN otherwise an INNER JOIN
  • If a LEFT JOIN relationship is filtered by anything but a predicate that operates on NULL values (e.g. IS NULL, COALESCE) then the join is promoted to an INNER JOIN

More details can be found from this gist that would benefit from being incorporate into eventual ORM specific documentation.

As for your particular issue on MySQL I’d suggest using the django-mysql package which provides a QuerySet.straight_join method to replace all INNER JOIN with STRAIGHT_JOIN.

It could also be implemented by changing all entries of queryset.query.alias_map that are isinstance(value, Join) and value.join_type == INNER to value.join_type = 'STRAIGHT_JOIN'.