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.
* 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>
* 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>
* status: new => assigned
* owner: nobody => animan1
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:3>
* easy: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:4>
* owner: animan1 =>
* status: assigned => new
* easy: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:5>
* easy: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:6>
* cc: aksheshdoshi@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:7>
* version: 1.5 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:8>
* 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>
* owner: => akki
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:10>
* 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>
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>
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>
* 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>
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>
* 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>
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>
* Attachment "20888-draft.diff" added.
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:18>
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:19>
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>
* status: new => closed
* resolution: => fixed
--
Ticket URL: <https://code.djangoproject.com/ticket/20888#comment:21>