Opened 8 years ago

Closed 7 years ago

Last modified 2 years ago

#11003 closed Uncategorized (wontfix)

Add USE INDEX, FORCE INDEX capabilities to ORM

Reported by: Renato Alves Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: INDEX USE FORCE
Cc: clouserw@…, rkm, Simon Litchfield Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no


Recently I've add significant performance issues with Django + MySQL.
The reasons behind this are MySQL not using an existing index due to low cardinality and instead do a full table scan. In most cases this is the correct choice but there some exceptions. For these, the workaround is to add "FORCE INDEX (index_name)" to the SQL.
However there is no support for this feature in the ORM.

As for the relevance of this functionality, in my case, query time was 0.09 sec when using FORCE INDEX and 10.3 sec when not.
This problem becomes more visible if the columns involved are used in several JOIN operations.

Attachments (1)

with-hints-13402.diff (3.0 KB) - added by Simon Litchfield 7 years ago.
Adds queryset.with_hints() support for MySQL

Download all attachments as: .zip

Change History (8)

comment:1 Changed 8 years ago by Adam Nelson

I agree, I think a parameter should be added to QuerySet.extra()

Maybe a key value like extra(final=None) for SQL to be appended to whatever the generated SQL is. Of course, this would not be portable.

comment:2 Changed 8 years ago by Alex Gaynor

Triage Stage: UnreviewedDesign decision needed

comment:3 Changed 7 years ago by Wil Clouser

Cc: clouserw@… added

comment:4 Changed 7 years ago by Russell Keith-Magee

Resolution: wontfix
Status: newclosed

This is one of those occasions where every database does something slightly different. MySQL uses FORCE INDEX in the query. Postgres uses configuration of query planner costs. SQLite does nothing (as far as I know). NoSQL backends (which are on the way) don't have any directly analog.

I don't see any obvious way to represent this problem in a generic fashion. I'm really not in favor of adding this to extra. Firstly, extra is already a nightmare of twisty dark passages without introducing additional complexity; secondly it's not obvious to me at all how extra(final=None) would map in a generic way to index use (especially given that you could have multiple joins on a single table, each requiring different index use). In my opinion, if you need to interact with the query at this level, you should be using a raw query, not trying to hack things into extra.

I'm going to mark this wontfix. Index use is a very important part of optimization, but that doesn't mean it has to be an important part of the ORM. That said, index use is an important part of optimization, so if anyone can make a good proposal for how to add index hinting support in an elegant and generic way, I'm open to further discussion.

comment:5 Changed 7 years ago by Simon Litchfield

Cc: rkm Simon Litchfield added
Has patch: set
Needs tests: set
Resolution: wontfix
Status: closedreopened

Here's a suggestion. See patch. Works on MySQL.

Model.objects.filter(field__fk1__fk2=value).with_hints('my_index', RelatedModel1='index1', RelatedModel2='index2')

Changed 7 years ago by Simon Litchfield

Attachment: with-hints-13402.diff added

Adds queryset.with_hints() support for MySQL

comment:6 Changed 7 years ago by Łukasz Rekucki

Resolution: wontfix
Status: reopenedclosed

Please do not reopen tickets marked as won't fix by core developers (I'm not a one), without prior discussion on django-developers. Send your proposal there and if consensus is reached, the ticked will be accepted.

comment:7 Changed 2 years ago by Dirk Bergstrom

Easy pickings: unset
Severity: Normal
Type: Uncategorized
UI/UX: unset
Note: See TracTickets for help on using tickets.
Back to Top