jsha
commented
9 years ago Fixed, sort of. Now we paginate over only the Blocks table, rather than a join of Blocks and TwitterUsers. Still in theory slow, but much faster than previously and probably good enough.
Right now we paginate using limit and offset, which is slow when offset is large because it has to scan a large number of rows. Instead we should find a way to do large offset queries using indices. These articles have some interesting ideas:
http://www.xarg.org/2011/10/optimized-pagination-using-mysql/ http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/
In the second one, 'maintain a page column' sounds promising, since it would be simple to do in update-blocks as we write to the DB. But it would increase the storage requirements unnecessarily for blocks that never get shown in a list. Possible leave the page column blank, but write it in at view time for faster lookups on future views? Or perhaps just do the queries expensively but cache at the Nginx layer for views of other people's block lists?