Efficient pagination of a SQL table with 100M records(allyouneedisbackend.com)

    1. max_heap_table_size
    2. read_buffer_size
    3. sort_buffer_size
    4. join_buffer_size
    5. thread_concurrency
    6. tmp_table_size

  -- first page
  SELECT name, id
  FROM users
  GROUP BY name, id
  ORDER BY name, id
  LIMIT 21;

  -- next page (ex: last item on current
  -- page has id 20 and name 'George')
  SELECT name, id
  FROM users
  WHERE name >= 'George' AND id > 20
  GROUP BY name, id
  ORDER BY name, id
  LIMIT 21;

  -- prev page (ex: first item on current
  -- page has id 21 and name 'Harry')
  SELECT name, id
  FROM users
  WHERE name <= 'Harry' AND id < 21
  GROUP BY name, id
  -- need DESC, reverse order in app for display
  ORDER BY name DESC, id DESC
  LIMIT 21;

  -- last page
  SELECT name, id
  FROM users
  GROUP BY name, id
  -- need DESC, reverse order in app for display
  ORDER BY name DESC, id DESC
  LIMIT 21;

  > You need to walk thru the table,
  > extract each record,
  > transform it inside your application’s code
  > and insert to another place.

  $items = db("SELECT a,b,c FROM item
           JOIN (SELECT id
              FROM item
              WHERE
              <your-where-conditions>
              ORDER BY id DESC
              LIMIT <offset>, <limit>)
              AS x ON
              x.id = item.id");