LIMIT in MySQL

The LIMIT clause in MySQL is used to restrict the number of rows returned by a query. It is useful for pagination, performance optimization, and testing.

1. Syntax

SELECT column1, column2, ...FROM table_nameLIMIT number_of_rows;

✅ Returns only number_of_rows records from the table.

2. Example Table: customers

3. Basic Usage of LIMIT

Retrieve the first 3 records

SELECT * FROM customers LIMIT 3;

✅ Output:

+----+---------+-------------+| id | name    | city        |+----+---------+-------------+| 1  | Alice   | New York    || 2  | Bob     | Los Angeles || 3  | Charlie | Chicago     |+----+---------+-------------+

4. LIMIT with OFFSET (Pagination)

SELECT * FROM customers LIMIT offset, count;

✅ offset → Number of rows to skip.
✅ count → Number of rows to return.

Get records from 3rd row onwards (skip first 2, fetch next 3)

SELECT * FROM customers LIMIT 2, 3;

✅ Output:

+----+---------+----------+| id | name    | city     |+----+---------+----------+| 3  | Charlie | Chicago  || 4  | David   | Houston  || 5  | Emma    | Miami    |+----+---------+----------+

🚀 Use for pagination:

• Page 1: LIMIT 0, 5
• Page 2: LIMIT 5, 5
• Page 3: LIMIT 10, 5

5. LIMIT with ORDER BY

SELECT * FROM customers ORDER BY name DESC LIMIT 3;

✅ Gets the last 3 names in descending order.

6. LIMIT with DELETE

DELETE FROM customers WHERE city = 'Miami' LIMIT 1;

✅ Deletes only 1 record, even if multiple match.

7. LIMIT with UPDATE

UPDATE customers SET city = 'Unknown' LIMIT 2;

✅ Updates only the first 2 rows in the table.

8. Performance Tips

⚡ Index columns used in ORDER BY for faster queries.
⚡ Avoid large OFFSET values (e.g., LIMIT 1000000, 10 is slow).
⚡ Use indexed pagination (WHERE id > last_id LIMIT 10) for better performance.

9. Key Takeaways

✅ LIMIT restricts the number of rows returned.
✅ LIMIT offset, count helps with pagination.
✅ Use LIMIT with ORDER BY for sorted results.
✅ Avoid large offsets for better performance.