A tutorial listing usually filters by category, sorts by date and shows a small page. Fetching every row and slicing the result in PHP wastes work. Start by moving the page boundary into SQL.

What you will build

Create a disposable tutorial table and compare a category-and-date query plan with a matching composite index.

Before you start

MySQL 8.0 and permission to create a table in a scratch database. Use a disposable database, not production tutorial data.

1. Create index_demo.sql

CREATE TABLE tutorial_index_demo (
    tutorialid BIGINT PRIMARY KEY,
    category VARCHAR(40) NOT NULL,
    published_on DATE NOT NULL,
    title VARCHAR(160) NOT NULL,
    INDEX ix_category_date_id (category, published_on DESC, tutorialid DESC)
);
INSERT INTO tutorial_index_demo VALUES
 (1, 'Java', '2026-11-05', 'Java HTTP basics'),
 (2, 'Java', '2026-11-06', 'Java JSON validation'),
 (3, 'Python', '2026-11-06', 'Python JSON basics'),
 (4, 'Java', '2026-11-06', 'Java AI client');
EXPLAIN SELECT tutorialid, title
FROM tutorial_index_demo
WHERE category = 'Java'
ORDER BY published_on DESC, tutorialid DESC
LIMIT 2;
SELECT tutorialid, title
FROM tutorial_index_demo
WHERE category = 'Java'
ORDER BY published_on DESC, tutorialid DESC
LIMIT 2;

2. Run the example

mysql your_scratch_database < index_demo.sql

Expected result

The result rows have IDs 4 and 2 in that order. EXPLAIN reports the optimizer’s chosen plan; a tiny table may still be scanned.

How it works

The index begins with category because the query fixes that value. The next columns match the requested ordering. tutorialid is a tie-breaker so rows sharing a date have a stable order.

An index is a candidate access path, not a command forcing the optimizer to use it. Compare plans and timings on representative data. Four demo rows cannot demonstrate a production performance improvement.

LIMIT bounds the returned page. For shallow pages, LIMIT with OFFSET can be adequate. Deep pagination may still examine many skipped rows; a cursor based on the last date and ID is a separate improvement worth measuring.

In PHP or Java, bind the category as a parameter. Validate page sizes and offsets as bounded integers. Avoid concatenating raw request values into SQL merely because the query now uses an index.

Test the edge cases

  • Insert another Java row with the same date and confirm the ID tie-breaker.
  • Try a category with no rows.
  • Run the script once in a fresh scratch database; it intentionally does not drop existing tables.

Keep learning

Browse related tutorials. Reference: official documentation.

Reviewed 7 November 2026. AI-dependent examples require your own local model; generated text varies.