What is the MySQL LIMIT Clause?

The LIMIT clause in MySQL restricts the number of rows returned by a query. It’s particularly useful for managing large datasets, improving performance, and implementing pagination.

Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT row_count;
  • row_count: The number of rows to return.

You can also specify an offset to skip a certain number of rows before returning the results:

SELECT column1, column2, ...
FROM table_name
LIMIT offset, row_count;
  • offset: The number of rows to skip.
  • row_count: The number of rows to return after the offset.

Examples of Using the LIMIT Clause

1. Retrieve the First 5 Rows

Fetch the first 5 employees from the employees table.

Query:

Example Output:

+----+---------+-------------+--------+
| id | name    | department  | salary |
+----+---------+-------------+--------+
| 1  | Alice   | HR          | 45000  |
| 2  | Bob     | Finance     | 55000  |
| 3  | Charlie | HR          | 60000  |
| 4  | David   | IT          | 70000  |
| 5  | Eve     | Marketing   | 40000  |
+----+---------+-------------+--------+

2. Pagination Example

Retrieve rows 6 to 10 from the employees table (useful for paginating results).

Query:

SELECT * FROM employees
LIMIT 5, 5;
  • Skips the first 5 rows and returns the next 5.

3. Combining LIMIT with ORDER BY

Fetch the top 3 highest-paid employees.

Query:

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 3;

4. LIMIT with OFFSET Using Separate Syntax

Another way to specify the offset:

SELECT * FROM employees
LIMIT 5 OFFSET 10;
  • Starts at row 11 (offset 10) and retrieves the next 5 rows.

Using LIMIT in MySQL Workbench

  1. Open MySQL Workbench.
  2. Write a query using the LIMIT clause in the SQL editor.
  3. Execute the query.
  4. View the limited results in the result grid.

Best Practices

Use with ORDER BY

Use with ORDER BY
Without an explicit ORDER BY, the rows returned by LIMIT might be unpredictable as the database does not guarantee any default order.

Optimize Pagination
Avoid large offsets in queries with LIMIT for pagination. Instead, use indexed columns or cursors for better performance.

Avoid Hardcoding
Use parameters for LIMIT values in your application to allow dynamic control over result limits.

Index the Filtered Columns
For large datasets, ensure columns used in WHERE or ORDER BY clauses are indexed to optimize performance.

Common Issues and Troubleshooting

  • Unpredictable Row Order:
    Always use ORDER BY with LIMIT to avoid random results.

  • Performance with Large Offsets:
    Avoid using large offsets as it can slow down queries on large datasets. Consider alternative pagination strategies.

  • Misinterpreted Offset Syntax:
    Remember that the offset starts from 0, not 1.