[Django] #20888: Index ordering in index_together and unique

Django

unread,

Aug 10, 2013, 6:03:18 AM8/10/13

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together
----------------------------------------------+--------------------------
Reporter: anonymous | Owner: nobody
Type: New feature | Status: new
Component: Database layer (models, ORM) | Version: 1.5
Severity: Normal | Keywords: index, order
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 1 | UI/UX: 0
----------------------------------------------+--------------------------
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?

--
Ticket URL: <https://code.djangoproject.com/ticket/20888>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Aug 17, 2013, 5:07:53 PM8/17/13

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------

Reporter: anonymous | Owner: nobody
Type: New feature | Status: new

Easy pickings: 1 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by wim@…):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:1>

Django

unread,

Aug 23, 2013, 7:25:33 AM8/23/13

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------

Reporter: anonymous | Owner: nobody
Type: New feature | Status: new

Component: Database layer | Version: 1.5
(models, ORM) | Resolution:

Severity: Normal | Triage Stage: Accepted
Keywords: index, order | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Changes (by akaariai):

* stage: Unreviewed => Accepted

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:2>

Django

unread,

Sep 6, 2013, 2:29:40 PM9/6/13

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: animan1
Type: New feature | Status: assigned

Changes (by animan1):

* status: new => assigned
* owner: nobody => animan1

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:3>

Django

unread,

Sep 18, 2013, 2:37:33 PM9/18/13

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by timo):

* easy: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:4>

Django

unread,

Sep 19, 2013, 11:58:09 AM9/19/13

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner:

Type: New feature | Status: new

Easy pickings: 1 |
-------------------------------------+-------------------------------------
Changes (by anonymous):

* owner: animan1 =>
* status: assigned => new
* easy: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:5>

Django

unread,

Sep 19, 2013, 12:04:58 PM9/19/13

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner:

Type: New feature | Status: new

Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by timo):

* easy: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:6>

Django

unread,

Mar 13, 2016, 3:05:39 PM3/13/16

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner:

Type: New feature | Status: new

Component: Database layer | Version: 1.5
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index, order | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by akki):

* cc: aksheshdoshi@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:7>

Django

unread,

Mar 14, 2016, 11:35:01 AM3/14/16

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner:

Type: New feature | Status: new

Component: Database layer | Version: master

Changes (by auvipy):

* version: 1.5 => master

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:8>

Django

unread,

May 14, 2016, 6:36:22 PM5/14/16

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner:

Type: New feature | Status: new

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: index, order, db- | Triage Stage: Accepted
indexes |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by timgraham):

* keywords: index, order => index, order, db-indexes

Old description:

> 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?

New description:

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?

--

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:9>

Django

unread,

Jul 25, 2016, 1:29:13 PM7/25/16

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: akki

Type: New feature | Status: assigned

Changes (by akki):

* owner: => akki

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:10>

Django

unread,

Jul 27, 2016, 4:04:24 PM7/27/16

to django-...@googlegroups.com

#20888: Index ordering in index_together and unique_together

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by akki):

* has_patch: 0 => 1

Comment:

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

Although requested for `alter_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 backend which
doesn't support the feature itself according to their
[http://dev.mysql.com/doc/refman/5.7/en/create-index.html official docs].
I felt this to be a bit weird as the reporter had requested the feature
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:11>

Django

unread,

Aug 4, 2016, 7:39:31 PM8/4/16

to django-...@googlegroups.com

#20888: Index ordering in index_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: akki
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index, order, db- | Triage Stage: Accepted
indexes |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by timgraham):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:12>

Django

unread,

Aug 12, 2016, 11:51:37 AM8/12/16

to django-...@googlegroups.com

#20888: Index ordering in index_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: akki
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: index, order, db- | Triage Stage: Accepted
indexes |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"f842d1011c1195aa26071a6ab6f96e0b8d907343" f842d10]:
{{{
#!CommitTicketReference repository=""
revision="f842d1011c1195aa26071a6ab6f96e0b8d907343"
Refs #20888 -- Added index order introspection.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:13>

Django

unread,

Aug 12, 2016, 4:16:21 PM8/12/16

to django-...@googlegroups.com

#20888: Index ordering in index_together

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: akki

Type: New feature | Status: closed

Component: Database layer | Version: master
(models, ORM) |

Severity: Normal | Resolution: fixed

Changes (by Tim Graham <timograham@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"311a8e8d505049ff5644a94e16c00246c8a43a18" 311a8e8]:
{{{
#!CommitTicketReference repository=""
revision="311a8e8d505049ff5644a94e16c00246c8a43a18"
Fixed #20888 -- Added support for column order in class-based indexes.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:14>

Django

unread,

Aug 13, 2016, 2:36:21 AM8/13/16

to django-...@googlegroups.com

#20888: Allow defining order of column for indexes

-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: akki
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: index, order, db- | Triage Stage: Accepted
indexes |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by akki):

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:15>

Django

unread,

Sep 29, 2016, 6:33:47 PM9/29/16

to django-...@googlegroups.com

#20888: Allow defining order of column for indexes
-------------------------------------+-------------------------------------

Reporter: anonymous | Owner: Akshesh
| Doshi

Type: New feature | Status: new

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: index, order, db- | Triage Stage: Accepted

indexes 1.11 |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* keywords: index, order, db-indexes => index, order, db-indexes 1.11
* status: closed => new
* has_patch: 1 => 0
* resolution: fixed =>

Comment:

We need to revisit this as on PostgreSQL 9.6, "column am.amcanorder does
not exist".

According to a post on the PostgreSQL mailing list: "Using
`pg_get_indexdef()` would leave you much less vulnerable to cross- version
differences in the system catalogs.

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:16>

Django

unread,

Oct 1, 2016, 12:44:18 PM10/1/16

to django-...@googlegroups.com

#20888: Allow defining order of column for indexes
-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: Akshesh
| Doshi

Type: New feature | Status: new

Comment (by Tim Graham):

I drafted a patch based on [https://github.com/pgjdbc/pgjdbc/pull/569 a
commit for pgjdbc] but it's not entirely working yet and has issues on
some older versions of PostgreSQL. I didn't spend too much time debugging.

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:17>

Django

unread,

Oct 1, 2016, 12:44:28 PM10/1/16

to django-...@googlegroups.com

#20888: Allow defining order of column for indexes
-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: Akshesh
| Doshi

Type: New feature | Status: new

Changes (by Tim Graham):

* Attachment "20888-draft.diff" added.

Django

unread,

Oct 14, 2016, 11:08:59 PM10/14/16

to django-...@googlegroups.com

#20888: Allow defining order of column for indexes
-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: Akshesh
| Doshi

Type: New feature | Status: new

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by François Freitag):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:18>

Django

unread,

Oct 15, 2016, 7:45:57 AM10/15/16

to django-...@googlegroups.com

#20888: Allow defining order of column for indexes
-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: Akshesh
| Doshi

Type: New feature | Status: new

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: index, order, db- | Triage Stage: Ready for
indexes 1.11 | checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:19>

Django

unread,

Oct 17, 2016, 7:16:35 AM10/17/16

to django-...@googlegroups.com

#20888: Allow defining order of column for indexes
-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: Akshesh
| Doshi

Type: New feature | Status: new

Comment (by Tim Graham <timograham@…>):

In [changeset:"86bb9a05020b8fadaab26f4f796c3c4d9adb8c5c" 86bb9a05]:
{{{
#!CommitTicketReference repository=""
revision="86bb9a05020b8fadaab26f4f796c3c4d9adb8c5c"
Refs #20888 -- Fixed index ordering introspection on PostgreSQL 9.6.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:20>

Django

unread,

Oct 17, 2016, 7:16:36 AM10/17/16

to django-...@googlegroups.com

#20888: Allow defining order of column for indexes
-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: Akshesh
| Doshi

Type: New feature | Status: closed

Component: Database layer | Version: master
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: index, order, db- | Triage Stage: Ready for
indexes 1.11 | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

[Django] #20888: Index ordering in index_together and unique_together

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django