VMware ESXi and vSphere Cluster Management

MySQL LIMIT Clause: Restricting Query Results

Learn how to use MySQL LIMIT to restrict SELECT results, skip rows with offsets, create predictable pages with ORDER BY, and understand performance considerations.

The MySQL LIMIT clause caps the number of rows in a query's result set. A result set is the collection of rows returned by a SQL query. LIMIT is useful when a table contains many records and you only need a preview, sample, or page-sized group of rows.

LIMIT affects the rows returned by the query. It does not change, delete, or modify the rows stored in the table.

Basic LIMIT syntax

The count-only form places a row count after LIMIT:

SELECT column_list
FROM table_name
LIMIT row_count;

row_count is the maximum number of rows MySQL returns. It is a maximum, so the query can return fewer rows if the table or other query conditions provide fewer qualifying rows.

For example, this query returns no more than five employee records:

SELECT *
FROM employees
LIMIT 5;

The asterisk selects all columns. In production queries, selecting only the columns you need can reduce the amount of data transferred and displayed.

LIMIT syntax forms

Using an offset with LIMIT

An offset is the number of result rows to skip before MySQL begins returning rows. Offsets are zero-based: offset 0 refers to the beginning of the result set.

Use the comma form to specify both the offset and the count:

SELECT *
FROM employees
LIMIT 2, 7;

This query skips the first two result rows and returns up to seven subsequent rows. The returned range begins with the third result row. The number skipped and the number returned are different values:

• 2 is the offset, so two rows are skipped.
• 7 is the row count, so up to seven rows are returned.

Make LIMIT results predictable with ORDER BY

LIMIT does not define which rows are considered first. Without an ORDER BY clause, MySQL does not guarantee a particular result order. The rows returned by a LIMIT query can therefore differ between executions.

Use ORDER BY before LIMIT when the result must have a meaningful and stable order. For example, sort employees by their employee number and then return the first five:

SELECT *
FROM employees
ORDER BY emp_no
LIMIT 5;

The logical pattern is:

SELECT ...
FROM ...
WHERE ...
ORDER BY ...
LIMIT ...;

ORDER BY determines the order, and LIMIT then restricts that ordered result. Choose a column, or combination of columns, that represents the order your application needs. An employee number or hire date can be suitable examples.

Common uses of LIMIT

Preview a large table

When inspecting a table, LIMIT prevents every row from being displayed:

SELECT *
FROM employees
LIMIT 5;

Test a query with a small sample

During query development, a small limit makes it easier to inspect columns and expressions without displaying a large result set.

Build page-sized result sets

Pagination presents a larger result set as smaller pages. If each page contains five rows, the first page can use an offset of zero, the next page an offset of five, and so on. Always apply an appropriate ORDER BY clause:

SELECT *
FROM employees
ORDER BY emp_no
LIMIT 10, 5;

This returns up to five employees after the first ten employees in employee-number order. It is a later page, not necessarily the eleventh through fifteenth rows of the table unless the ordering remains the same.

LIMIT and performance

Returning fewer rows can reduce result-transfer and display overhead. This is especially helpful when a client, application, or user interface does not need the entire result set.

However, LIMIT alone does not necessarily make the whole query inexpensive. MySQL may still need to:

• Find and filter many rows before selecting the limited results.
• Sort qualifying rows when ORDER BY is present.
• Pass over many earlier rows when the offset is large.

Indexes can help MySQL filter rows efficiently and retrieve rows in a useful order. The appropriate index depends on the WHERE and ORDER BY expressions. Use query-analysis tools such as EXPLAIN when investigating a slow query.

For very deep pages, offset pagination can become increasingly expensive because the database still has to work past the skipped rows. An advanced alternative is keyset pagination, which requests rows after the last value from the previous page, using an indexed ordering column.

Troubleshooting LIMIT queries

The rows change between executions

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

Fix: Add an ORDER BY clause before LIMIT:

SELECT *
FROM employees
ORDER BY emp_no
LIMIT 5;

LIMIT 2, 7 does not include the second displayed row

Cause: The offset counts rows to skip and starts at zero.

Fix: Offset 2 skips the first and second result rows. The returned range begins at result row three and contains up to seven rows.

A later page is slow

Cause: A large offset can require MySQL to work past many earlier rows, especially when sorting is required or useful indexes are absent.

Fix: Check whether filtering and ordering columns are appropriately indexed. For very deep pagination, consider keyset pagination.

LIMIT returns fewer rows than requested

Cause: Fewer qualifying rows remain after filtering and offset processing.

Fix: Treat the row count as a maximum, not a promise that exactly that many rows exist.

Key points

• LIMIT restricts the number of rows returned by a query; it does not modify stored data.
• LIMIT 5 returns at most five rows.
• LIMIT 2, 7 skips two zero-based result rows and returns up to seven rows.
• Use ORDER BY before LIMIT when the first rows must be predictable.
• LIMIT is useful for previews, testing, and pagination.
• LIMIT can reduce transfer and display work, but sorting, filtering, and large offsets can still be expensive.

For more practice with this topic, continue with the MySQL LIMIT clause lesson.