Add USE INDEX, FORCE INDEX capabilities to ORM

[Renato Alves]

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.

[Adam Nelson]

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.

[Russell Keith-Magee]

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.

[Simon Litchfield]

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

[Simon Litchfield]

Adds queryset.with_hints() support for MySQL

[Łukasz Rekucki]

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.

[Dirk Bergstrom]

The django-mysql extension supports indexing hints:
​http://django-mysql.readthedocs.org/en/latest/queryset_extensions.html?highlight=index#django_mysql.models.use_index