MySQL LIMIT Clause

Learn how to use MySQL LIMIT to restrict SELECT results, skip rows with offsets, sort results reliably, and build simple pagination.

The MySQL LIMIT clause caps the number of rows returned by a query. It is especially useful when a table contains thousands or millions of rows and you need only a small part of the result set.

A result set is the collection of rows returned by a database query. LIMIT changes only the result returned by the current SELECT statement. It does not delete, hide permanently, or modify records in the table.

Common uses include previewing a table, inspecting sample data, avoiding unnecessarily large result sets, showing the newest records, selecting top-ranked values, and implementing pagination.

Prerequisites: You should understand basic SELECT statements, tables, rows, columns, and SELECT *. See Query a Database and Database Terms for related fundamentals.

Basic LIMIT Syntax

The simplest form places a single number after LIMIT:

SELECT column_list
FROM table_name
LIMIT row_count;

row_count is the maximum number of rows MySQL should return. For example:

SELECT * FROM employees LIMIT 5;

This query returns at most five rows. If the table contains fewer than five matching rows, MySQL returns only the rows that exist.

Example: Return Five Employee Rows

Assume an employees table with columns such as emp_no, birth_date, first_name, last_name, gender, and hire_date.

SELECT *
FROM employees
LIMIT 5;

The query selects all columns but returns no more than five records. The underlying table may contain thousands of employees; LIMIT does not reduce the number of records stored in the table.

Syntax: LIMIT row_count
Meaning: Return up to row_count rows.
Example: LIMIT 5 returns at most five rows.

Skipping Rows with an Offset

An offset tells MySQL how many result rows to skip before it starts returning rows. The two-number form is:

SELECT column_list
FROM table_name
LIMIT offset, row_count;

In this form:

offset is the number of result rows skipped.
row_count is the maximum number of rows returned after the skipped rows.
Offsets are zero-based. Offset 0 means that MySQL begins with the first result row.

For example:

SELECT *
FROM employees
LIMIT 2, 7;

LIMIT 2, 7 skips the first two result rows, at offsets 0 and 1, and returns up to the next seven rows. When enough rows exist, the visible records correspond to positions three through nine.

Offset: 0
Rows skipped: 0
First returned position: first row

Offset: 2
Rows skipped: 2
First returned position: third row

Offset: 20
Rows skipped: 20
First returned position: 21st row

Understanding the Offset Calculation

Because offsets start at zero, the first result row has offset 0, the second has offset 1, and the third has offset 2. Therefore, LIMIT 2, 7 excludes the first two rows and begins returning data with the third row.

If fewer than seven rows remain after the offset, MySQL returns fewer than seven rows. An offset beyond the end of the matching result set produces an empty result set.

Use ORDER BY for Meaningful Results

A SELECT query without ORDER BY does not guarantee a stable or meaningful row order. The first five rows can vary because the database is free to access matching rows in an order that is convenient for its execution plan.

ORDER BY defines the order of the result set before LIMIT is applied. Use it whenever “first,” “newest,” “oldest,” “highest,” or “lowest” has a specific meaning.

SELECT emp_no, first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC
LIMIT 5;

This query sorts employees by hiring date from newest to oldest and then returns the five most recently hired employees. LIMIT is applied to the sorted result, so the returned rows have a defined purpose.

For reliable pagination, sort by a deterministic column or combination of columns. A primary key is often useful as a tie-breaker:

SELECT emp_no, first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC, emp_no DESC
LIMIT 5;

Adding emp_no makes the order more precise when multiple employees have the same hire date. See Sort Results for more information about ORDER BY.

Common LIMIT Use Cases

Preview a Large Table

SELECT * FROM employees LIMIT 5;

This is a convenient way to inspect the table structure and sample data without retrieving every row.

Show Recent Records

SELECT emp_no, first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC
LIMIT 5;

The descending sort places the newest hire dates first, and LIMIT selects the first five rows from that ordered result.

Retrieve Top-Ranked Values

SELECT emp_no, first_name, last_name
FROM employees
ORDER BY emp_no DESC
LIMIT 10;

The meaning of “top” comes from ORDER BY. LIMIT only caps how many rows are returned.

Build a Page of Results

Pagination means dividing a long result set into smaller pages. If each page contains 10 rows, the first pages can use these offsets:

Page 1: offset 0, so LIMIT 0, 10
Page 2: offset 10, so LIMIT 10, 10
Page 3: offset 20, so LIMIT 20, 10

A page query might look like this:

SELECT emp_no, first_name, last_name
FROM employees
ORDER BY emp_no
LIMIT 20, 10;

This query skips the first 20 ordered rows and returns up to 10 rows for the next page. The formula is:

offset = (page_number - 1) * page_size

For example, page 3 with a page size of 10 has an offset of (3 - 1) * 10 = 20.

Pagination Caveats

Offset pagination is easy to understand, but large offsets can become inefficient on large tables. MySQL may need to find and skip many rows before it can return the requested page.

Results can also shift between requests when rows are inserted, deleted, or updated. A user might see a duplicate row or miss a row if the underlying data changes between page requests.

For deep pages or frequently changing data, consider keyset pagination, also called cursor-style pagination. Instead of repeatedly skipping a growing number of rows, the next query uses the last value from the previous page as a starting point. For example, with an increasing employee number:

SELECT emp_no, first_name, last_name
FROM employees
WHERE emp_no > 1200
ORDER BY emp_no
LIMIT 10;

This approach can be more efficient when the ordering and filtering columns are properly indexed. It also provides a clearer continuation point than a large offset.

LIMIT and Data Modification

In a SELECT statement, LIMIT restricts only the rows returned to the client. It does not remove rows from employees, change column values, or reduce the table size.

SELECT * FROM employees LIMIT 5;

The query above is read-only. Data modification requires statements such as UPDATE, INSERT, or DELETE, each of which should be used deliberately and tested carefully. Do not confuse a small displayed result with a smaller table.

Troubleshooting LIMIT Queries

The First Records Are Not the Ones Expected

Likely cause: The query has no ORDER BY clause, so the result order is not guaranteed.

Resolution: Add ORDER BY using the column that defines the intended order, then apply LIMIT.

SELECT *
FROM employees
ORDER BY emp_no
LIMIT 5;

LIMIT 2, 7 Appears to Start at the Third Row

Likely cause: MySQL offsets are zero-based.

Resolution: Use offset 0 for the first row, offset 1 for the second row, and offset 2 for the third row. Thus, LIMIT 2, 7 correctly starts with the third result row.

Fewer Rows Are Returned Than Requested

Likely causes: There are not enough matching rows after the offset, or a WHERE condition reduced the result set.

Resolution: Check the filtering conditions, the number of matching rows, and the offset. LIMIT specifies a maximum, not a promise that the requested count will always be available.

Later Pages Are Slow

Likely cause: A large offset requires MySQL to skip many rows.

Resolution: Use a suitable ORDER BY and WHERE condition, ensure relevant columns are appropriately indexed, and consider keyset pagination for deep pages.

Exam-Relevant Notes

LIMIT restricts the number of rows returned by a query.
LIMIT 5 returns at most five rows.
In LIMIT offset, row_count, the offset is the number of rows skipped and row_count is the maximum number returned.
Offsets begin at zero, so offset 2 starts with the third result row.
ORDER BY should be used when the selected rows must have a defined order.
LIMIT in a SELECT statement does not delete or modify table data.
Large offsets can be inefficient, making keyset or cursor-style pagination a useful alternative.

Summary

LIMIT controls the size of a SELECT result set. Use the single-number form, LIMIT row_count, to return only the first part of a result. Use LIMIT offset, row_count to skip an initial set of rows and return the next portion.

For predictable results, sort with ORDER BY before applying LIMIT. This is essential when selecting recent records, top-ranked values, or pages of data. LIMIT is a read-time restriction: it affects what the query returns, not what is stored in the table.