How to implement a paginator that doesn't call count(*)

Question

I am working on a django website that has a MySQL innodb backend. We have hundreds of thousands of records in several of our tables and this is causing some site stability/performance issues in the admin. Specifically, django likes to make count(*) queries when creating the paginators, and this is causing lots of problems.

With Django 1.3.x, they started to allow for custom pagination classes to be provided. So, I'm interested in finding a way to appropriately speed up or eliminate these queries. So far, I've been looking at these two pages: http://code.google.com/p/django-pagination/source/browse/trunk/pagination/paginator.py https://gist.github.com/1094682 and have not really found them to be what I'm looking for. Any suggestions, help, ect. would be much appreciated.

Edson

Hundreds of thousands of records is nothing. Fire your DBA and find one that knows what they're doing. – Basilbasilar

a) moving to a new DB is not an option for a number of reasons b) my dba's name is django, and as much as I'd love to fire him, I'd rather not rebuild the entire application – Edson

DBA = Database administrator, and I'm assuming that you don't have one. If your system is failing with hundreds of thousands of rows then the problem lies with your db config/hardware. Hundreds of thousands of rows is really nothing to a DB. But Cat plus plus is right...mysql sucks – Griggs

Correct, we do not have a dedicated DBA... but that probably would not do much considering django auto-generates the tables, and large parts of the admin are auto-generated as well. MySQL sucks, yes I know. I did not build this system, I just get to maintain it. I regularly work with much larger datasets than this on different databases and have no problems, unfortunately switching databases at this point is not something we have the budget for. As much as I'd love to hear about other things I can do to fix this. I need a programmatic solution, not a software or hardware. – Edson

A database server is more than just the schema. – Basilbasilar

You care to point out a way to get innodb to return count queries quickly? I'm kinda sure this is something that innodb just sucks at, regardless of what our server setup is. Which, again, is not changing. Not sure if you read that part the 2nd time I said it, but I need a programmatic solution not a hardware or software solution. I'd love to have an infinite budget to do that, but I don't, so we're stuck with what we have. So, less stating of the obvious and more solutions would be better. – Edson

ffs, not one useful comment. 'Firing your DBA'? Very helpful – Beefy

is this of any help ? #3327861 – Define

We'll probably end up writing our own, all we really care about is there a next / prev page or not. – Malorie

Answer 1

You can define _count variable in your paginator

  paginator = Paginator(QuerySet, 300)
  paginator._count = 9000 # or use some query here

And here is the part of django paginator code to help you understand what this variable do and how page count works

def _get_count(self):
    "Returns the total number of objects, across all pages."
    if self._count is None:
        try:
            self._count = self.object_list.count()
        except (AttributeError, TypeError):
            # AttributeError if object_list has no count() method.
            # TypeError if object_list.count() requires arguments
            # (i.e. is of type list).
            self._count = len(self.object_list)
    return self._count
count = property(_get_count)

Bork

This is an old question but it makes sense to add a small clarification: if you subclass Paginator and set your _count value in the Paginator.__init__() method, make sure you set the value after you have called super().__init__(), since the base Paginator sets _count and _num_pages to None (which defeats the purpose of the exercise). – Oligarch

@Bork paginator._count = 9000 does not have any effect. Still count query is being fired – Kop

@Kop setting paginator.count instead of paginator._count worked for me here – Parisian

Answer 2

You can pass the count by yourself

paginator = Paginator(models ,25 )
paginator.count=100000

or if you want the exact count you can use

count=int(model.objects.latest('id').id)
paginator = Paginator(models ,25 )
paginator.count=count

Tannenberg

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Armentrout

Answer 3

You could also check out django-endless-pagination.endless_pagination.paginator.LazyPaginator is not bad, but you might need to add a few tweaks.

Dilettantism