LIMIT Number of Rows to Fetch in MySQL

MySQL LIMIT Clause for Restricting Rows

Use the MySQL LIMIT clause when a SELECT query should return no more than a specified number of rows. It is commonly used for previews, top-N queries, search results, and pagination. In this MySQL Tutorial, you will learn the basic syntax, how to use LIMIT with ORDER BY and WHERE, and how offsets work.

To limit the number of rows/records a SELECT query can fetch append your SQL Query with LIMIT modifier and the syntax of the SQL Query is given below:

your_sql_query LIMIT number_of_records;

The value supplied to LIMIT is the maximum number of rows that MySQL returns. If fewer rows match the query, MySQL returns all matching rows. The value must be a nonnegative integer.

MySQL LIMIT Syntax Forms

MySQL supports a row-count form and two equivalent offset forms. The offset is zero-based, so offset 0 starts with the first row.

SELECT column_list
FROM table_name
LIMIT row_count;

SELECT column_list
FROM table_name
LIMIT offset, row_count;

SELECT column_list
FROM table_name
LIMIT row_count OFFSET offset;

LIMIT form	Meaning
LIMIT 5	Return at most five rows.
LIMIT 5, 10	Skip five rows and return at most ten rows.
LIMIT 10 OFFSET 5	The same result range as LIMIT 5, 10.

MySQL LIMIT Query Examples

The following queries limit all rows from the students table or limit only rows that satisfy a WHERE condition.

 SELECT * FROM students LIMIT 2;
 SELECT * FROM students WHERE section='B' LIMIT 2;

Example 1 – Fetch Two Rows with MySQL LIMIT

Consider the following students table. It has four records in total.

The following query requests no more than two rows from the table:

SELECT * FROM students LIMIT 2;

Because this query has no ORDER BY clause, it does not define which two rows must appear. Add an explicit order when the application needs a predictable result.

MySQL LIMIT with ORDER BY for Predictable Results

LIMIT restricts the size of the result but does not choose a meaningful order by itself. Use ORDER BY to define whether you need the smallest, largest, oldest, newest, or otherwise ranked rows.

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 2;

This query returns the two rows with the lowest student_id values. To fetch the two highest values instead, use descending order.

SELECT *
FROM students
ORDER BY student_id DESC
LIMIT 2;

When the primary sort column can contain duplicate values, add a unique column as a tie-breaker. This keeps repeated executions and pagination in a stable order.

SELECT student_id, name, marks
FROM students
ORDER BY marks DESC, student_id ASC
LIMIT 3;

MySQL LIMIT with WHERE Conditions

A WHERE clause filters rows before the final result is limited. The next query returns at most two students from section B, ordered by student_id.

SELECT *
FROM students
WHERE section = 'B'
ORDER BY student_id
LIMIT 2;

If only one row matches the condition, the result contains one row even though the limit is two.

MySQL LIMIT OFFSET for Pagination

Pagination divides a result into pages. For a page size of ten rows, the first page uses offset 0, the second uses offset 10, and the third uses offset 20.

-- Page 1: rows 1 through 10
SELECT *
FROM students
ORDER BY student_id
LIMIT 10 OFFSET 0;

-- Page 2: rows 11 through 20
SELECT *
FROM students
ORDER BY student_id
LIMIT 10 OFFSET 10;

-- Page 3: rows 21 through 30
SELECT *
FROM students
ORDER BY student_id
LIMIT 10 OFFSET 20;

The offset for a requested page can be calculated as (page_number - 1) * page_size. Always use the same deterministic ORDER BY expression on every page.

Meaning of LIMIT 3, 1 in MySQL

In the comma syntax, the first number is the offset and the second is the maximum row count. Therefore, LIMIT 3, 1 skips the first three rows and returns one row—the fourth row in the ordered result.

SELECT *
FROM students
ORDER BY student_id
LIMIT 3, 1;

Keyset Pagination Instead of a Large MySQL OFFSET

Large offsets can become inefficient because MySQL may still need to find and skip many earlier rows. When results are ordered by an indexed unique column, keyset pagination can continue after the last value returned by the previous page.

SELECT *
FROM students
WHERE student_id > 100
ORDER BY student_id
LIMIT 10;

Here, 100 represents the last student_id from the preceding page. This approach is suitable for next-page navigation, but offset pagination remains useful when users must jump directly to an arbitrary page number.

Where MySQL LIMIT Is Used in Applications

Web and mobile applications use LIMIT for search results, product lists, activity feeds, admin tables, previews, and paginated reports. Fetching only the rows needed for the current view can reduce the amount of result data transferred and processed by the application.

A limit does not automatically make every query fast. MySQL may still examine or sort many rows before returning the limited result, especially when filters and ordering columns are not supported by suitable indexes. Use EXPLAIN to inspect slow queries, and consider an index that matches frequently used WHERE and ORDER BY columns.

EXPLAIN
SELECT *
FROM students
WHERE section = 'B'
ORDER BY student_id
LIMIT 20;

For the complete clause rules, see the MySQL SELECT statement reference.

Common MySQL LIMIT Mistakes

• Using LIMIT without ORDER BY: the query restricts the count but does not guarantee which rows are returned.
• Reversing the comma arguments: in LIMIT offset, row_count, the offset comes first.
• Treating the offset as one-based: MySQL offsets start at zero.
• Ordering by a non-unique column only: duplicate sort values can produce unstable page boundaries; add a unique tie-breaker.
• Assuming LIMIT avoids all database work: a query may still scan or sort many rows before producing the result.

MySQL LIMIT Query QA Checklist

• Confirm that the requested row count and offset are nonnegative integers.
• Add a topic-appropriate ORDER BY clause when the selected rows must be predictable.
• Use a unique tie-breaker in the sort order for stable pagination.
• Verify that LIMIT offset, row_count has not been confused with LIMIT row_count OFFSET offset.
• Run EXPLAIN when a limited query remains slow, particularly with large offsets.

MySQL LIMIT Frequently Asked Questions

How do I limit a MySQL query to 10 rows?

Add LIMIT 10 to the end of the SELECT statement. Add ORDER BY before it when you need a defined set, such as the ten newest or ten highest rows.

Does MySQL LIMIT return the first rows in a table?

It returns up to the requested number of rows, but “first” has no reliable meaning unless the query includes ORDER BY. Table storage order should not be used as an application-level ordering rule.

What is the difference between LIMIT and OFFSET in MySQL?

LIMIT sets the maximum number of rows to return. OFFSET sets how many rows to skip before returning them. For example, LIMIT 10 OFFSET 20 skips 20 ordered rows and returns up to 10.

Is LIMIT 5, 10 the same as LIMIT 10 OFFSET 5?

Yes. Both forms skip five rows and return at most ten rows.

Can MySQL LIMIT be used with UPDATE or DELETE?

MySQL supports LIMIT in single-table UPDATE and DELETE statements. Use a restrictive WHERE clause and a deterministic ORDER BY when the affected rows must be controlled, and verify the matching rows with a SELECT query before modifying data.