PHP Database Design and Pagination: Constraints, Indexes, Joins, and Stable Pages

Database Shape

Prepared statements protect variable values, but they do not design correct data. Constraints preserve invariants, indexes support real access paths, joins combine related records, and pagination must use a deterministic order.

After this lesson, you can choose keys and constraints for a feature, align an index with a query, recognize an N+1 loop, and implement stable keyset pagination with PDO.

Schema Constraints

Give every table a primary key, use foreign keys for required relationships, choose nullability deliberately, and enforce uniqueness where the business rule is truly global. Application validation provides friendly messages; database constraints remain the final integrity boundary.

Tasks with Ownership

CREATE TABLE tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(160) NOT NULL,
    completed_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_tasks_user
        FOREIGN KEY (user_id) REFERENCES users(id)
);

The foreign key prevents a task from referencing a user row that does not exist.

Index by Query

Design an index from the columns used for equality filtering and deterministic ordering. An index on every column increases write cost and rarely matches compound access patterns.

Support a User Task Feed

CREATE INDEX idx_tasks_user_created_id
    ON tasks (user_id, created_at DESC, id DESC);

SELECT id, title, created_at
FROM tasks
WHERE user_id = :user_id
ORDER BY created_at DESC, id DESC
LIMIT 20;

The id tie-breaker makes ordering stable when two rows share the same timestamp.

Join Instead of N Plus One

A loop that queries the owner for every task turns one list request into many round trips. Select the required related columns in one join or load a bounded related set deliberately.

Load Task and Owner

SELECT t.id, t.title, u.display_name
FROM tasks AS t
JOIN users AS u ON u.id = t.user_id
WHERE t.user_id = :user_id
ORDER BY t.created_at DESC, t.id DESC
LIMIT 20;

Pagination Choice

Approach	Use When	Tradeoff
Offset	Small lists and direct page numbers	Deep pages scan or skip more rows and can shift during writes
Keyset	Feeds and next-page navigation	Needs ordered cursor values and no arbitrary page jump

Keyset Query

Send the final ordered values from the previous page as an opaque cursor. Bind each value and repeat the exact order in the comparison.

Fetch the Next Task Page

<?php
$sql = <<<'SQL'
SELECT id, title, created_at
FROM tasks
WHERE user_id = :user_id
  AND (created_at < :created_at
       OR (created_at = :created_at AND id < :id))
ORDER BY created_at DESC, id DESC
LIMIT 20
SQL;

$statement = $pdo->prepare($sql);
$statement->execute([
    'user_id' => 42,
    'created_at' => '2026-07-12 10:00:00',
    'id' => 900,
]);

The compound cursor follows the same created_at and id ordering as the query.

Plan Inspection

Use EXPLAIN with representative data rather than guessing that an index is used.
Select only columns the response needs.
Measure query count and duration around the complete request.
Treat schema migrations and index creation as deployable, reviewed code.

Review the Access Path

0 of 2 checked

Q1. Why include id after created_at in the order?

To make ties deterministic To disable the index To encrypt the cursor

Q2. What does an N+1 query usually look like?

One list query followed by one query per row One prepared insert One transaction

Try this next

Design a Query Path

0 of 2 completed

Index a filtered feed
Write the equality filters, order, limit, and matching compound index for an incomplete-task list.
Remove an N+1 loop
Count queries in a task-and-owner page, replace repeated lookups with a join, and compare the result shape.

Previous Next