防止 django 管理员在列表表单上运行 SELECT COUNT(*) [英] Prevent django admin from running SELECT COUNT(*) on the list form

问题描述

每次我使用 Admin 列出模型的条目时，Admin 都会计算表中的行数.更糟糕的是，即使您在过滤查询时，它似乎也会这样做.

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.

例如，如果我只想显示 id 为 123、456、789 的模型，我可以这样做:

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

哪个杀了mysql+innodb.问题似乎得到部分承认在这张票中，但我的问题似乎更具体，因为它计算所有行，即使它不应该.

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?

注意:我使用的是 django 1.2.7.

Note: I am using django 1.2.7.

推荐答案

好吧，我想我找到了解决方案.正如彼得所建议的，最好的方法是处理 count 属性，它可以通过使用自定义查询集覆盖它来完成(如 这篇文章) 专门用于计数的近似等价物:

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)

然后在管理员中:

class MyAdmin(admin.ModelAdmin):

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

近似函数可能会在第 100000 页上搞砸，但对我来说已经足够了.

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

这篇关于防止 django 管理员在列表表单上运行 SELECT COUNT(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！