Opened 15 years ago

Closed 14 years ago

Last modified 8 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: dev
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

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 Simon Litchfield 14 years ago.
Adds queryset.with_hints() support for MySQL

Download all attachments as: .zip

Change History (8)

comment:1 by Adam Nelson, 15 years ago

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 by Alex Gaynor, 15 years ago

Triage Stage: UnreviewedDesign decision needed

comment:3 by Wil Clouser, 14 years ago

Cc: clouserw@… added

comment:4 by Russell Keith-Magee, 14 years ago

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 by Simon Litchfield, 14 years ago

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=value).with_hints('my_index')
Model.objects.filter(field__fk1__fk2=value).with_hints('my_index', RelatedModel1='index1', RelatedModel2='index2')

by Simon Litchfield, 14 years ago

Attachment: with-hints-13402.diff added

Adds queryset.with_hints() support for MySQL

comment:6 by Łukasz Rekucki, 14 years ago

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 by Dirk Bergstrom, 8 years ago

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