title: How to paginate more than 10M of records efficiently
body: <p>I need to paginate more than 30M users which are hosted on MySQL. I'm displaying 15 users per page, but it's quite slow. My goal is to access to any random page and load it in a few ms.</p>
  <p>At the beginning, I was using the offset method for MySQL, but as I said, is quite slow (and a bad idea for sure). Then I moved to ElasticSearch, but you still have some window limit, so you are limited. After that, I have been checking different ways like the &quot;cursor&quot; method, but I can not access to any random page. For example, we start at the first page, and we have 100000 pages, I would like to access the 4782th page, and load it in a few ms. With the cursor method, I'm just able to access the next &amp;&amp; prev page, and the &quot;scroll&quot; method doesn't fit what I really need.</p>
  <p>My users' ID are not sorted just by ID, so I can not use it as a delimiter. Already thought about <a href="https://explainextended.com/2011/02/11/late-row-lookups-innodb/" rel="nofollow noreferrer">Late row lookups</a></p>
  <p>I don't mind to move all my data to a new DB (but would be to find different solutions). Here Amazon does it really well (<a href="https://www.amazon.com/review/top-reviewers" rel="nofollow noreferrer">https://www.amazon.com/review/top-reviewers</a>)</p>
  <p>Query using offset:</p>
  <pre><code>SELECT users.* from users
  WHERE users.country = 'DE'
  ORDER BY users.posts_count DESC, users.id DESC
  LIMIT 15 OFFSET 473
  </code></pre>
  <p>PD: My user list is almost in real-time, so it's changing every hour.</p>
  <p>Any ideas? Thanks a lot!</p>