Prevent django admin from running SELECT COUNT(*) on the list form

Question

Every time I use Admin to list the entries of a model, the Admin count the rows in the table. Worse yet, it seems to be doing so even when you are filtering your query.

For instance if I want to show only the models whose id is 123, 456, 789 I can do:

/admin/myapp/mymodel/?id__in=123,456,789

But the queries ran (among others) are:

SELECT COUNT(*) FROM `myapp_mymodel` WHERE `myapp_mymodel`.`id` IN (123, 456, 789) # okay
SELECT COUNT(*) FROM `myapp_mymodel` # why???

Which is killing mysql+innodb. It seems that the problem is partially acknowledged in this ticket, but my issue seems more specific since it counts all the rows even if it is not supposed to.

Is there a way to disable that global rows count?

Note: I am using django 1.2.7.

Answer 1

Django 1.8 lets you disable this by setting show_full_result_count = False.

https://docs.djangoproject.com/en/stable/ref/contrib/admin/#django.contrib.admin.ModelAdmin.show_full_result_count

Answer 2

Okay, I think I found a solution. As Peter suggested, the best approach is to work on the count property and it can be done by overriding it with custom query set (as seen in this post) that specialises the count with an approximate equivalent:

from django.db import connections, models
from django.db.models.query import QuerySet

class ApproxCountQuerySet(QuerySet):
    """Counting all rows is very expensive on large Innodb tables. This
    is a replacement for QuerySet that returns an approximation if count()
    is called with no additional constraints. In all other cases it should
    behave exactly as QuerySet.

    Only works with MySQL. Behaves normally for all other engines.
    """

    def count(self):
        # Code from django/db/models/query.py

        if self._result_cache is not None and not self._iter:
            return len(self._result_cache)

        is_mysql = 'mysql' in connections[self.db].client.executable_name.lower()

        query = self.query
        if (is_mysql and not query.where and
                query.high_mark is None and
                query.low_mark == 0 and
                not query.select and
                not query.group_by and
                not query.having and
                not query.distinct):
            # If query has no constraints, we would be simply doing
            # "SELECT COUNT(*) FROM foo". Monkey patch so the we
            # get an approximation instead.
            cursor = connections[self.db].cursor()
            cursor.execute("SHOW TABLE STATUS LIKE %s",
                    (self.model._meta.db_table,))
            return cursor.fetchall()[0][4]
        else:
            return self.query.get_count(using=self.db)

Then in the admin:

class MyAdmin(admin.ModelAdmin):

    def queryset(self, request):
        qs = super(MyAdmin, self).queryset(request)
        return qs._clone(klass=ApproxCountQuerySet)

The approximate function could mess things up on page number 100000, but it is good enough for my case.

Answer 3

I found Nova's answer very helpful, but i use postgres. I modified it slightly to work for postgres with some slight alterations to handle table namespaces, and slightly different "detect postgres" logic.

Here's the pg version.

class ApproxCountPgQuerySet(models.query.QuerySet):
  """approximate unconstrained count(*) with reltuples from pg_class"""

  def count(self):
      if self._result_cache is not None and not self._iter:
          return len(self._result_cache)

      if hasattr(connections[self.db].client.connection, 'pg_version'):
          query = self.query
          if (not query.where and query.high_mark is None and query.low_mark == 0 and
              not query.select and not query.group_by and not query.having and not query.distinct):
              # If query has no constraints, we would be simply doing
              # "SELECT COUNT(*) FROM foo". Monkey patch so the we get an approximation instead.
              parts = [p.strip('"') for p in self.model._meta.db_table.split('.')]
              cursor = connections[self.db].cursor()
              if len(parts) == 1:
                  cursor.execute("select reltuples::bigint FROM pg_class WHERE relname = %s", parts)
              else:
                  cursor.execute("select reltuples::bigint FROM pg_class c JOIN pg_namespace n on (c.relnamespace = n.oid) WHERE n.nspname = %s AND c.relname = %s", parts)
          return cursor.fetchall()[0][0]
      return self.query.get_count(using=self.db)

Answer 4

The Nova's solution (ApproxCountQuerySet) works great, however in newer versions of Django queryset method got replaced with get_queryset, so it now should be:

class MyAdmin(admin.ModelAdmin):

    def get_queryset(self, request):
        qs = super(MyAdmin, self).get_queryset(request)
        return qs._clone(klass=ApproxCountQuerySet)