Opened 3 years ago

Closed 2 weeks ago

Last modified 2 weeks ago

#20888 closed New feature (fixed)

Allow defining order of column for indexes

Reported by: anonymous Owned by: akki
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: index, order, db-indexes
Cc: aksheshdoshi@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by timgraham)

Hi,

Currently the index_together tuples do not support index ordering using + / -.

If I have the following table:

class Messages:
   user_id,
   rating,
   last_update,

   index_together=[
     ['user_id', 'rating',  'last_update',] # sorted ASC by default
  ]

So for MySQL, the following query cannot make use of the index:

Messages.objects.filter(user_id=xxx).order_by('+rating','-last_update')

Pardon me if it actually can make use of the index.

I know there are workarounds, such as negating values to make a DESC ordering become ASC ordering.
But it is just nice to have such a feature.
Btw, what is the reason for not having this feature?

Change History (15)

comment:1 Changed 3 years ago by wim@…

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

Hi anonymous, thanks for taking the time to fill in a ticket. Just to answer your question: most features are not implemented because people did not have time to write the code for it. :) So if you are willing to write a pull request and contribute such a feature could land in Django.

Unfortunately I do not understand MySQl enough to mark this ticket as accepted.

comment:2 Changed 3 years ago by akaariai

  • Triage Stage changed from Unreviewed to Accepted

To me this seems acceptable. Of course, supporting this only on MySQL isn't good, I believe most backends support index ordering.

comment:3 Changed 3 years ago by animan1

  • Owner changed from nobody to animan1
  • Status changed from new to assigned

comment:4 Changed 3 years ago by timo

  • Easy pickings unset

comment:5 Changed 3 years ago by anonymous

  • Easy pickings set
  • Owner animan1 deleted
  • Status changed from assigned to new

comment:6 Changed 3 years ago by timo

  • Easy pickings unset

comment:7 Changed 6 months ago by akki

  • Cc aksheshdoshi@… added

comment:8 Changed 6 months ago by auvipy

  • Version changed from 1.5 to master

comment:9 Changed 3 months ago by timgraham

  • Description modified (diff)
  • Keywords db-indexes added

comment:10 Changed 5 weeks ago by akki

  • Owner set to akki
  • Status changed from new to assigned

comment:11 Changed 4 weeks ago by akki

  • Has patch set

PR: https://github.com/django/django/pull/6982

Although requested for alter_index_together, I have implemented the feature with the new class based indexes which would allow users to do the same thing.

The patch contains implementation for all databases except MySQL which doesn't support the feature itself according to their official docs. I felt this to be a bit weird as originally the feature had been requested specifically for MySQL. Please let me know if I missed something in this context.

The patch doesn't add the feature for unique_together as I couldn't understand/don't know what that means. I can try to add it for unique_together as well if somebody could explain that.

Last edited 3 weeks ago by akki (previous) (diff)

comment:12 Changed 3 weeks ago by timgraham

  • Summary changed from Index ordering in index_together and unique_together to Index ordering in index_together

I'll retitle the ticket and we can open a new one if there's more to be done with respect to unique_together.

comment:13 Changed 2 weeks ago by Tim Graham <timograham@…>

In f842d10:

Refs #20888 -- Added index order introspection.

comment:14 Changed 2 weeks ago by Tim Graham <timograham@…>

  • Resolution set to fixed
  • Status changed from assigned to closed

In 311a8e8:

Fixed #20888 -- Added support for column order in class-based indexes.

comment:15 Changed 2 weeks ago by akki

  • Summary changed from Index ordering in index_together to Allow defining order of column for indexes

Since technically we haven't fixed the ticket described by the earlier summary, but in the end it's the feature that matters so won't reopen this and renaming it instead.

Note: See TracTickets for help on using tickets.
Back to Top