#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)
Change History (8)
comment:1 Changed 14 years ago by
comment:2 Changed 14 years ago by
Triage Stage: | Unreviewed → Design decision needed |
---|
comment:3 Changed 13 years ago by
Cc: | clouserw@… added |
---|
comment:4 Changed 13 years ago by
Resolution: | → wontfix |
---|---|
Status: | new → 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 13 years ago by
Cc: | rkm Simon Litchfield added |
---|---|
Has patch: | set |
Needs tests: | set |
Resolution: | wontfix |
Status: | closed → 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 13 years ago by
Attachment: | with-hints-13402.diff added |
---|
Adds queryset.with_hints() support for MySQL
comment:6 Changed 13 years ago by
Resolution: | → wontfix |
---|---|
Status: | reopened → 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.
comment:7 Changed 8 years ago by
Easy pickings: | unset |
---|---|
Severity: | → Normal |
Type: | → Uncategorized |
UI/UX: | unset |
The django-mysql extension supports indexing hints:
http://django-mysql.readthedocs.org/en/latest/queryset_extensions.html?highlight=index#django_mysql.models.use_index
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.