LIMIT Clause in MySQL
Introduction
When working with large databases, retrieving all records at once is often unnecessary and inefficient.
The LIMIT clause in MySQL allows you to control how many rows are returned by a query.
It is widely used for:
- Pagination (web apps, APIs)
- Performance optimization
- Displaying top or limited results
What is a LIMIT Clause
The LIMIT clause is used to restrict the number of rows returned in a query result.
It is primarily used with the SELECT statement, but can also be used with UPDATE and DELETE (in MySQL).
Basic Syntax
SELECT column_name
FROM table_name
LIMIT number_of_rows;
Example
SELECT * FROM Students
LIMIT 5;
This returns only the first 5 rows from the Students table.
LIMIT with OFFSET
The OFFSET is used to skip a certain number of rows before returning results.
Syntax (Comma Style)
SELECT column_name
FROM table_name
LIMIT offset, count;
Example
SELECT * FROM Students
LIMIT 5, 5;
Meaning:
- Skip first 5 rows
- Return the next 5 rows
Alternative Syntax (OFFSET Keyword)
SELECT * FROM Students
LIMIT 5 OFFSET 5;
Both syntaxes are equivalent.
LIMIT with ORDER BY
LIMIT is often used with ORDER BY to get meaningful results.
SELECT * FROM Students
ORDER BY age DESC
LIMIT 3;
This returns the top 3 students with the highest age.
LIMIT with WHERE Clause
LIMIT works after filtering.
SELECT * FROM Students
WHERE age > 18
LIMIT 5;
LIMIT in UPDATE and DELETE
MySQL allows LIMIT in UPDATE and DELETE.
UPDATE with LIMIT
UPDATE Students
SET status = 'Active'
LIMIT 2;
DELETE with LIMIT
DELETE FROM Students
LIMIT 3;
Pagination Using LIMIT
LIMIT is widely used for pagination.
Formula
LIMIT (page_number - 1) * page_size, page_size;
Example
- Page 1 → LIMIT 0, 10
- Page 2 → LIMIT 10, 10
- Page 3 → LIMIT 20, 10
LIMIT with JOIN
LIMIT can be used with JOIN queries:
SELECT Students. name, Courses.course_name
FROM Students
JOIN Courses ON Students.id = Courses.student_id
LIMIT 5;
LIMIT with Subqueries
SELECT * FROM (
SELECT * FROM Students
ORDER BY age DESC
) AS temp
LIMIT 3;
Execution Order with LIMIT
LIMIT is applied after:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- LIMIT
Performance Considerations
- LIMIT improves performance by reducing the data returned
- Large OFFSET values can slow down queries
- For large datasets, use indexed columns with ORDER BY
Important Notes
- LIMIT without ORDER BY may give inconsistent results
- OFFSET starts from 0 (zero-based index)
- LIMIT does not guarantee order unless ORDER BY is used
- Works differently across databases (MySQL-specific syntax)
Example Scenario
Get the top 5 highest-scoring students:
SELECT * FROM Students
ORDER BY marks DESC
LIMIT 5;
Common Mistakes
- Using LIMIT without ORDER BY
- Confusing LIMIT with WHERE
- Using incorrect OFFSET values
- Expecting consistent results without sorting
- Using a large OFFSET is causing slow queries
Key Points to Remember
- LIMIT restricts the number of rows returned
- OFFSET skips rows before fetching
- Used for pagination and performance
- Works with SELECT, UPDATE, and DELETE (MySQL)
- Best used with ORDER BY for consistent results