django admin incorrectly adds order by into query

Question

I have noticed thanks to django debug toolbar, that every django admin list page, always add an "ORDER BY id DESC" to all my queries, EVEN if I manually override the get_queryset method of the admin.ModelAdmin (which I usually do because I want custom sorting on some of my admin pages)

I guess this is not really something to worry about, but it is an additional sorting operation the database will need to do, even if it doesn't make sense at all.

Is there any way to prevent this? It seems like on some models (even that, not on all) if I add the ordering meta data, then it won't automatically add an order by by id, but it will however add by that field, which is something also I don't want, because doing so would add that order by to all my other queries across the code.

EDIT: Seems like the culprit is at django.contrib.admin.views.main at ChangeList, on the function get_ordering at line 316 (django 1.7.10)

 pk_name = self.lookup_opts.pk.name
    if not (set(ordering) & set(['pk', '-pk', pk_name, '-' + pk_name])):
        # The two sets do not intersect, meaning the pk isn't present. So
        # we add it.
        ordering.append('-pk')

I wonder what's the reason behind this...

EDIT: To improve performance, and since MySQL (and InnoDB) returns data in the clustered index order when no order by is given, I can safely remove that id appending. To do so, it is quite easy, I have just extended django's ChangeList and modified the get_ordering method. After that, just made a custom admin model that extendes from ModelAdmin and overrides the get_changelist method to the rerturn the above class.

I hope it helps anyone :)

Answer 1

Was having the exact same issue as this question where an admin queryset was 4 times slower due to the ID sort when I already have unique sorts. Thanks to @user1777914 and his work I don't have timeouts every other load! I am just adding this answer here for clarity if others suffer the same. As user1777914 mentions extend the ChangeList:

class NoPkChangeList(ChangeList):
    def get_ordering(self, request, queryset):
        """
        Returns the list of ordering fields for the change list.
        First we check the get_ordering() method in model admin, then we check
        the object's default ordering. Then, any manually-specified ordering
        from the query string overrides anything. Finally, WE REMOVE the primary
        key ordering field.
        """
        params = self.params
        ordering = list(self.model_admin.get_ordering(request) or self._get_default_ordering())
        if ORDER_VAR in params:
            # Clear ordering and used params
            ordering = []
            order_params = params[ORDER_VAR].split('.')
            for p in order_params:
                try:
                    none, pfx, idx = p.rpartition('-')
                    field_name = self.list_display[int(idx)]
                    order_field = self.get_ordering_field(field_name)
                    if not order_field:
                        continue  # No 'admin_order_field', skip it
                    # reverse order if order_field has already "-" as prefix
                    if order_field.startswith('-') and pfx == "-":
                        ordering.append(order_field[1:])
                    else:
                        ordering.append(pfx + order_field)
                except (IndexError, ValueError):
                    continue  # Invalid ordering specified, skip it.

        # Add the given query's ordering fields, if any.
        ordering.extend(queryset.query.order_by)

        # Ensure that the primary key is systematically present in the list of
        # ordering fields so we can guarantee a deterministic order across all
        # database backends.
        # pk_name = self.lookup_opts.pk.name
        # if not (set(ordering) & {'pk', '-pk', pk_name, '-' + pk_name}):
        #     # The two sets do not intersect, meaning the pk isn't present. So
        #     # we add it.
        #     ordering.append('-pk')

        return ordering

Then in your ModelAdmin just override get_changelist:

class MyAdmin(ModelAdmin):
    def get_changelist(self, request, **kwargs):
        return NoPkChangeList

Answer 2

The answer of 7Wonders can be reduced to the following statements because only ChangeList._get_deterministic_ordering() needs to change:

# admin.py
class MyAdmin(ModelAdmin):
    def get_changelist(self, request, **kwargs):
        """Improve changelist query speed by disabling deterministic ordering.

        Please be aware that this might disturb pagination.
        """
        from django.contrib.admin.views.main import ChangeList

        class NoDeterministicOrderChangeList(ChangeList):
            def _get_deterministic_ordering(self, ordering):
                return ordering

        return NoDeterministicOrderChangeList