Load More / Infinite Scroll with PHP and MySQL: Why OFFSET Is the Wrong Tool

A "Load More" button is straightforward to build and almost always built on a foundation that degrades badly. The standard LIMIT n OFFSET m approach gets slower with every page and silently duplicates or skips rows when the underlying data changes.

This guide covers both: the modern front end with the Fetch API and no jQuery, and keyset pagination on the back end, which fixes both problems.

The problem with OFFSET

There are two distinct failures here.

Performance. Page 1 is instant. Page 5,000 reads a hundred thousand rows to return twenty. On a large table the last pages can take seconds.

Correctness. Offsets are positions, not identities. If someone inserts a new row while a visitor is reading page 1, every subsequent row shifts down one — so the first item of page 2 is the one they already saw. Delete a row and an item is skipped entirely. On any feed sorted newest-first with active writes, this happens constantly.

Keyset pagination

Instead of "skip 100 rows", say "give me rows after this one". The cursor is the last value you saw:

-- First page
SELECT id, name, message, created_at
FROM posts
ORDER BY id DESC
LIMIT 20;

-- Subsequent pages — :cursor is the id of the last row returned
SELECT id, name, message, created_at
FROM posts
WHERE id < :cursor
ORDER BY id DESC
LIMIT 20;

With an index on id, MySQL seeks directly to the cursor. Page 5,000 costs exactly what page 1 costs. And because the cursor identifies a row rather than a position, inserts and deletes elsewhere cannot shift the window.

Sorting by a non-unique column

If you order by something that can tie — created_at, a score — you need a tiebreaker, or rows at the boundary get lost or repeated:

SELECT id, name, created_at
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

That row-value comparison is supported by MySQL and does the right thing. It needs a composite index:

CREATE INDEX idx_posts_created_id ON posts (created_at DESC, id DESC);

The trade-off with keyset pagination is that you cannot jump to an arbitrary page number — there is no "page 47" without knowing where it starts. For a Load More button or infinite scroll, which only ever move forward, that costs nothing.

The API endpoint

<?php
declare(strict_types=1);

header('Content-Type: application/json');

const PER_PAGE = 20;

$pdo = new PDO('mysql:host=localhost;dbname=demo;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// A cursor of 0 or absent means "start at the beginning".
$cursor = filter_input(INPUT_GET, 'cursor', FILTER_VALIDATE_INT) ?: null;

if ($cursor === null) {
    $stmt = $pdo->prepare(
        'SELECT id, name, message, created_at FROM posts ORDER BY id DESC LIMIT :lim'
    );
} else {
    $stmt = $pdo->prepare(
        'SELECT id, name, message, created_at FROM posts WHERE id < :cursor ORDER BY id DESC LIMIT :lim'
    );
    $stmt->bindValue(':cursor', $cursor, PDO::PARAM_INT);
}

// LIMIT will not accept a string placeholder — bind it as an integer explicitly.
$stmt->bindValue(':lim', PER_PAGE + 1, PDO::PARAM_INT);
$stmt->execute();

$rows = $stmt->fetchAll();

// Fetching one extra row tells us whether more exist, with no COUNT(*) query.
$hasMore = count($rows) > PER_PAGE;
if ($hasMore) {
    array_pop($rows);
}

echo json_encode([
    'items'       => $rows,
    'next_cursor' => $hasMore && $rows ? end($rows)['id'] : null,
], JSON_THROW_ON_ERROR);

Two details that matter.

Fetching PER_PAGE + 1 rows is how you know whether a "Load More" button should still be shown, without running a separate SELECT COUNT(*). On a large table that count is often slower than the query it accompanies.

bindValue(':lim', …, PDO::PARAM_INT) is required. By default PDO quotes parameters as strings, and LIMIT '20' is a syntax error in MySQL. This catches people constantly.

Return JSON, not HTML. The old approach echoed markup from PHP, which meant the server dictated presentation and every template change required a backend deploy.

The front end, without jQuery

const list    = document.querySelector('#results');
const button  = document.querySelector('#load-more');
const status  = document.querySelector('#load-status');

let cursor = null;
let loading = false;

async function loadMore() {
  if (loading) return;           // guard against double-clicks
  loading = true;
  button.disabled = true;
  status.textContent = 'Loading…';

  try {
    const url = new URL('/api/posts.php', location.origin);
    if (cursor !== null) url.searchParams.set('cursor', cursor);

    const res = await fetch(url, { headers: { Accept: 'application/json' } });
    if (!res.ok) throw new Error(`Request failed: ${res.status}`);

    const { items, next_cursor } = await res.json();

    // Build once, insert once — appending inside the loop forces a reflow per item.
    const frag = document.createDocumentFragment();
    for (const item of items) {
      const li = document.createElement('li');
      li.className = 'result';

      const name = document.createElement('span');
      name.className = 'result__name';
      name.textContent = item.name;          // textContent, not innerHTML — no XSS

      const msg = document.createElement('p');
      msg.className = 'result__message';
      msg.textContent = item.message;

      li.append(name, msg);
      frag.append(li);
    }
    list.append(frag);

    cursor = next_cursor;
    status.textContent = '';

    if (cursor === null) {
      button.remove();
      status.textContent = 'No more results.';
    }
  } catch (err) {
    status.textContent = 'Could not load more. Please try again.';
    console.error(err);
  } finally {
    loading = false;
    button.disabled = cursor === null;
  }
}

button.addEventListener('click', loadMore);
loadMore();   // initial page

Note textContent rather than innerHTML. If any of that data is user-supplied — and in a comments or posts feed it is — innerHTML is a stored XSS vulnerability. textContent cannot execute anything.

The loading flag matters more than it looks. Without it, an impatient double-click fires two requests with the same cursor and appends every row twice.

Turning it into infinite scroll

Use IntersectionObserver rather than a scroll handler. Scroll events fire dozens of times a second and need throttling; the observer fires only when the sentinel actually becomes visible:

const sentinel = document.querySelector('#scroll-sentinel');

const observer = new IntersectionObserver((entries) => {
  if (entries[0].isIntersecting && cursor !== null) loadMore();
}, {
  rootMargin: '400px',   // start fetching before the user reaches the bottom
});

observer.observe(sentinel);

A word of caution on infinite scroll: it makes the footer unreachable, breaks the back button unless you sync state to the URL, and hurts accessibility because keyboard and screen reader users get no warning that content is appearing. A "Load More" button avoids all three. If you do use infinite scroll, keep an explicit button as well and announce new content in an aria-live="polite" region.

When OFFSET is still fine

Keyset pagination is not always the answer. Stay with LIMIT/OFFSET when you need numbered pages ("Page 3 of 47"), when users must jump to an arbitrary page, or when the table is small enough that depth never becomes a problem — a few thousand rows will never notice the difference.

The rule of thumb: forward-only feeds and large tables want keyset; admin tables with page numbers and modest row counts are fine with OFFSET.

Summary

• Use keyset pagination for feeds — constant cost at any depth, and immune to shifting rows.
• Bind LIMIT with PDO::PARAM_INT, or the query will not parse.
• Fetch one extra row instead of running COUNT(*) to detect more pages.
• Return JSON and render on the client.
• Use textContent when injecting user data.
• Guard against concurrent requests with a loading flag.
• Prefer IntersectionObserver to scroll listeners, and prefer a button to infinite scroll.