Code

Opened 5 years ago

Closed 4 years ago

#11003 closed (wontfix)

Add USE INDEX, FORCE INDEX capabilities to ORM

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

Description

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 simon29 4 years ago.
Adds queryset.with_hints() support for MySQL

Download all attachments as: .zip

Change History (7)

comment:1 Changed 5 years ago by adamnelson

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

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

http://docs.djangoproject.com/en/dev/ref/models/querysets/#extra-select-none-where-none-params-none-tables-none-order-by-none-select-params-none

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 5 years ago by Alex

  • Triage Stage changed from Unreviewed to Design decision needed

comment:3 Changed 4 years ago by clouserw

  • Cc clouserw@… added

comment:4 Changed 4 years ago by russellm

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

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 4 years ago by simon29

  • Cc rkm, simon29 added
  • Has patch set
  • Needs tests set
  • Resolution wontfix deleted
  • Status changed from closed to reopened

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

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

Changed 4 years ago by simon29

Adds queryset.with_hints() support for MySQL

comment:6 Changed 4 years ago by lrekucki

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

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.