LIMIT and OFFSET in MySQL

LIMIT and OFFSET

LIMIT caps returned rows; OFFSET skips rows from an ordered result. They are useful for previews, top-N reports and pagination, but correctness begins with deterministic ORDER BY.

SELECT student_id, full_name, marks
FROM students
ORDER BY marks DESC, student_id ASC
LIMIT 2;

MySQL Syntax Forms

-- Return at most row_count rows
LIMIT row_count

-- Skip offset rows, then return row_count
LIMIT row_count OFFSET offset

-- MySQL comma form: offset first, count second
LIMIT offset, row_count

Page Calculation

For page size 2: page 1 offset 0, page 2 offset 2, page 3 offset 4. Formula:

offset = (page_number - 1) * page_size

SELECT student_id, full_name, marks
FROM students
ORDER BY marks DESC, student_id ASC
LIMIT 2 OFFSET 2;

Validate page as a positive integer, cap page_size and bind permitted numeric values through the driver where supported.

Deterministic Ordering

Without ORDER BY, storage or execution changes can move rows between pages. Even ORDER BY marks DESC is incomplete if marks tie. Add a unique final key:

ORDER BY marks DESC, student_id ASC

When data changes between page requests, offset pages can still show duplicates or miss rows. Choose pagination consistency according to the application.

Large OFFSET and Keyset Pagination

Deep offset pagination can require scanning and discarding many earlier rows. For a “next” feed ordered by student_id, keyset pagination is simpler:

SELECT student_id, full_name
FROM students
WHERE student_id > ?
ORDER BY student_id ASC
LIMIT 20;

Bind the last student_id from the previous page. For multi-column ordering, the seek condition must reproduce the complete ordering tuple. Keyset pagination is fast and stable for next/previous navigation but does not naturally jump to an arbitrary page number.

Mistakes and Practice

• LIMIT without ORDER BY.
• Reversing comma-form arguments.
• No maximum page size.
• Ignoring ties.
• Using huge OFFSET for an endless feed.

1. Return the top three marks deterministically.
2. Write page 2 with size 2.
3. Rewrite ID-based next-page navigation as keyset pagination.
4. Explain why new inserts can shift offset pages.

Quick Summary

• LIMIT caps rows and OFFSET skips ordered rows.
• Use ORDER BY plus a unique tie-breaker.
• Validate and cap pagination inputs.
• Keyset pagination avoids large-offset discard cost for sequential navigation.

Official References

• SELECT / LIMIT Syntax — MySQL 8.4
• LIMIT Query Optimization
• ORDER BY Optimization

References reviewed 14 August 2026.