LIMIT and OFFSET for pagination in MySQL

Introduction

LIMIT and OFFSET help you get a small part of data from a big list. This is useful when you want to show data page by page.

Showing 10 products per page on an online store.

Displaying 5 comments at a time under a blog post.

Loading 20 users per page in an admin panel.

Fetching a few rows from a large table to improve speed.

Syntax

MySQL

SELECT columns FROM table
LIMIT number_of_rows OFFSET skip_rows;

LIMIT sets how many rows to get.

OFFSET sets how many rows to skip before starting to get rows.

Examples

Get the first 5 rows from employees table.

MySQL

SELECT * FROM employees
LIMIT 5;

Skip first 10 rows, then get next 5 rows.

MySQL

SELECT * FROM employees
LIMIT 5 OFFSET 10;

Another way in MySQL: skip 10 rows, then get 5 rows.

MySQL

SELECT * FROM employees
LIMIT 10, 5;

Sample Program

This creates a small employees table with 10 rows. Then it selects 3 rows starting after skipping 4 rows.

MySQL

CREATE TABLE employees (id INT, name VARCHAR(20));
INSERT INTO employees VALUES
(1, 'Alice'), (2, 'Bob'), (3, 'Carol'), (4, 'Dave'), (5, 'Eve'),
(6, 'Frank'), (7, 'Grace'), (8, 'Hank'), (9, 'Ivy'), (10, 'Jack');

SELECT * FROM employees
LIMIT 3 OFFSET 4;

Important Notes

OFFSET starts counting from zero, so OFFSET 0 means start from the first row.

Using LIMIT without OFFSET gets rows from the start.

In MySQL, you can also write LIMIT offset, count instead of LIMIT count OFFSET offset.

Summary

LIMIT controls how many rows you get.

OFFSET controls where to start getting rows.

They help show data page by page in a simple way.