17

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.

ebensing
  • 6,409
  • 4
  • 18
  • 20
  • 6
    Hundreds of thousands of records is nothing. Fire your DBA and find one that knows what they're doing. – Ignacio Vazquez-Abrams Oct 09 '11 at 22:56
  • 3
    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 – ebensing Oct 09 '11 at 23:21
  • 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 – John Oct 10 '11 at 03:04
  • 3
    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. – ebensing Oct 10 '11 at 04:52
  • 1
    A database server is more than just the schema. – Ignacio Vazquez-Abrams Oct 10 '11 at 05:35
  • 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. – ebensing Oct 10 '11 at 06:25
  • 6
    ffs, not one useful comment. 'Firing your DBA'? Very helpful – Timmy O'Mahony Oct 10 '11 at 07:10
  • is this of any help ? http://stackoverflow.com/questions/3327361/should-i-avoid-count-all-together-in-innodb/3327367#3327367 – Tommaso Barbugli Oct 10 '11 at 20:28
  • We'll probably end up writing our own, all we really care about is there a next / prev page or not. – Kevin Parker Nov 21 '19 at 14:35

3 Answers3

15

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)
errx
  • 1,761
  • 4
  • 18
  • 25
  • 1
    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). – pgcd Sep 23 '14 at 08:29
  • 2
    @errx paginator._count = 9000 does not have any effect. Still count query is being fired – Jenish Dec 03 '17 at 12:40
  • 1
    @Jenish setting `paginator.count` instead of `paginator._count` worked for me here – Wilhelm Klopp Jan 21 '22 at 22:48
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
Ahmed Mag
  • 21
  • 4
  • 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](/help/how-to-ask). – Community Sep 11 '21 at 22:53
-1

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

Gabriel
  • 734
  • 11
  • 26
DrMeers
  • 4,117
  • 2
  • 36
  • 38