MySql - Limiting results with LIMIT

Limiting Results with LIMIT 

In SQL development, especially when working with large datasets, it's often necessary to restrict the number of rows returned by a query. MySQL provides a powerful feature called LIMIT to achieve this. It allows you to specify how many records to return and optionally how many records to skip before starting the result set. LIMIT is essential for tasks such as pagination, previewing data, optimizing performance, and debugging.

This guide offers a comprehensive deep dive into the MySQL LIMIT clause, including syntax, examples, use cases, performance tips, and advanced usage with ORDER BY, OFFSET, joins, subqueries, and more.

1. Basic Syntax of LIMIT

Single Argument Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

Dual Argument Syntax (With Offset)

SELECT column1, column2, ...
FROM table_name
LIMIT offset, number_of_rows;

2. Simple LIMIT Usage

Fetch the first 5 rows from a table:

SELECT * FROM employees
LIMIT 5;

This returns the first 5 rows from the employees table.

3. Using LIMIT with ORDER BY

It’s common to combine LIMIT with ORDER BY to return top or bottom results in a specific order.

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10;

This returns the top 10 highest-paid employees.

Lowest Salary Example

SELECT * FROM employees
ORDER BY salary ASC
LIMIT 5;

4. LIMIT with OFFSET

Syntax Recap

SELECT * FROM table_name
LIMIT offset, count;

Example: Skip 5 and Get Next 10

SELECT * FROM products
LIMIT 5, 10;

This skips the first 5 products and returns the next 10.

5. LIMIT with Pagination

Pagination is essential in web applications for displaying results page-by-page.

Example: Page 1 (records 0 to 9)

SELECT * FROM books
LIMIT 0, 10;

Example: Page 2 (records 10 to 19)

SELECT * FROM books
LIMIT 10, 10;

General Formula

SELECT * FROM table_name
LIMIT (page_number - 1) * page_size, page_size;

6. Dynamic LIMIT in Stored Procedures

Stored Procedure Example

DELIMITER //

CREATE PROCEDURE GetLimitedEmployees(IN offset_val INT, IN row_count INT)
BEGIN
  SELECT * FROM employees
  ORDER BY hire_date DESC
  LIMIT offset_val, row_count;
END //

DELIMITER ;

7. Using LIMIT with DELETE

LIMIT can be used with DELETE to remove rows in batches.

DELETE FROM logs
ORDER BY created_at ASC
LIMIT 100;

This deletes the oldest 100 logs. Useful for log cleanup automation.

8. LIMIT with UPDATE

MySQL also supports LIMIT in UPDATE queries (with ORDER BY):

UPDATE tasks
SET status = 'completed'
WHERE status = 'pending'
ORDER BY priority DESC
LIMIT 5;

This marks the top 5 high-priority pending tasks as completed.

9. LIMIT with Subqueries

You can use LIMIT in subqueries to filter input to a larger query.

Example: Fetch Employee with Second-Highest Salary

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Or equivalently:

SELECT * FROM (
  SELECT * FROM employees
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1
) AS second_top_earner;

10. LIMIT with Joins

Example: Top 5 Customers with Orders

SELECT c.customer_name, COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY total_orders DESC
LIMIT 5;

11. LIMIT in Views

LIMIT can be used in a view, but it may have restrictions based on the version of MySQL and use case.

View Example

CREATE VIEW top_customers AS
SELECT customer_id, customer_name
FROM customers
ORDER BY total_spent DESC
LIMIT 10;

12. LIMIT with UNION

(SELECT * FROM employees WHERE department = 'Sales')
UNION
(SELECT * FROM employees WHERE department = 'IT')
LIMIT 10;

This combines employees from Sales and IT and limits the final result to 10 rows.

13. LIMIT in Prepared Statements

PREPARE stmt FROM 'SELECT * FROM employees LIMIT ?, ?';
SET @offset = 0, @count = 5;
EXECUTE stmt USING @offset, @count;
DEALLOCATE PREPARE stmt;

14. Performance Considerations

• LIMIT is fast with small datasets but may become slower with large OFFSET values.
• Always use ORDER BY with LIMIT for deterministic results.
• Consider using indexed columns for ORDER BY to improve performance.

Use LIMIT with Indexed ORDER BY

SELECT * FROM users
ORDER BY user_id ASC
LIMIT 1000, 10;

This skips 1000 rows, which can be slow for huge tables. Consider keyset pagination as an alternative.

15. Keyset Pagination Alternative

Instead of using OFFSET, use WHERE on indexed key:

SELECT * FROM orders
WHERE order_id > 5000
ORDER BY order_id ASC
LIMIT 10;

This is significantly faster for large datasets and reduces read latency.

16. Testing LIMIT Queries

Use LIMIT to test queries safely without retrieving or modifying large datasets.

Example

SELECT * FROM large_table
LIMIT 10;

17. Troubleshooting Common Errors

• Missing ORDER BY: Result order may vary without ORDER BY
• Incorrect LIMIT position: LIMIT must appear at the end of the query
• LIMIT with GROUP BY: Must use ORDER BY to ensure correct ranking

18. LIMIT with OFFSET vs Row Numbers

MySQL does not support ROW_NUMBER() directly in LIMIT, but with MySQL 8.0+ you can use:

SELECT employee_id, name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees
LIMIT 10;

19. LIMIT in Applications (PHP, Node.js, Python)

Node.js Pagination

let page = 2;
let limit = 10;
let offset = (page - 1) * limit;

connection.query('SELECT * FROM products LIMIT ?, ?', [offset, limit], ...);

20. LIMIT vs FETCH FIRST (ANSI SQL)

MySQL uses LIMIT, while ANSI SQL uses FETCH:

-- MySQL
SELECT * FROM employees LIMIT 5;

-- ANSI SQL
SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

21. Edge Cases

• LIMIT 0 – Returns no rows
• Negative values – Treated as 0 or invalid (depends on MySQL version)
• LIMIT beyond row count – Returns only available rows

Example: LIMIT beyond data

SELECT * FROM employees
LIMIT 10000, 50;

If there are only 1000 employees, this will return 0 rows.

The LIMIT clause is an essential feature in MySQL for controlling the size of the result set. Whether you are working on pagination, testing, top-N reporting, or performance tuning, LIMIT offers a flexible and efficient mechanism to retrieve data. When combined with ORDER BY, OFFSET, and indexes, it becomes a robust tool for building scalable SQL queries.

To maximize its effectiveness, always use it in combination with indexed columns, and consider alternatives like keyset pagination when dealing with large OFFSETs. With proper use, LIMIT improves readability, performance, and user experience in data-driven applications.