Faster pagination / model object seeking (10x faster infact :o) for larger datasets (500k +)
ModelPagination Designed and Coded by Cal Leeming Many thanks to Harry Roberts for giving us a heads up on how to do this properly! ---------------------------------------------------------------------------- This is a super optimized way of paginating datasets over 1 million records. It uses MAX() rather then COUNT(), because this is super faster. EXAMPLE: >>> _t = time.time(); x = Post.objects.aggregate(Max('id')); "Took %ss"%(time.time() - _t ) 'Took 0.00103402137756s' >>> _t = time.time(); x = Post.objects.aggregate(Count('id')); "Took %ss"%(time.time() - _t ) 'Took 0.92404794693s' >>> This does mean that if you go deleting things, then the IDs won't be accurate, so if you delete 50 rows, you're exact count() isn't going to match, but this is okay for pagination, because for SEO, we want items to stay on the original page they were scanned on. If you go deleting items, then the items shift backwards through the pages, so you end up with inconsistent SEO on archive pages. If this doesn't make sense, go figure it out for yourself, its 2am in the morning ffs ;p Now, the next thing we do, is use id seeking, rather then OFFSET, because again, this is a shitton faster: EXAMPLE: >>> _t = time.time(); x = map(lambda x: x, Post.objects.filter(id__gte=400000, id__lt=400500).all()); print "Took %ss"%(time.time() - _t) Took 0.0467309951782s >>> _t = time.time(); _res = map(lambda x: x, Post.objects.all()[400000:400500]); print "Took %ss"%(time.time() - _t) Took 1.05785298347s >>> By using this seeking method (which btw, can be implemented on anything, not just pagination) on a table with 5 million rows, we are saving 0.92s on row count, and 1.01s on item grabbing. This may not seem like much, but if you have 1024 concurrent users, this will make a huge difference. If you have any questions or problems, feel free to contact me on cal.leeming [at] simplicitymedialtd.co.uk
- model
- pagination
- object
- large
- big
- dataset
- faster
- optimized
- quicker
- seeking