How To Limit The Number Of Rows Returned In MySQL

As a database administrator or a web developer, you may have faced the situation where you need to retrieve only a limited number of rows from a large dataset in your MySQL database. In this post, I will show you how to use the LIMIT clause in MySQL to achieve this.

Before you keep reading, I just wanted to let you know about our modern MySQL client, Beekeeper Studio. Open a table and it pages through the rows for you, no LIMIT needed, and it’s free to use for MySQL, download it here.

The MySQL LIMIT Clause

The LIMIT clause is used to limit the number of rows returned in a SELECT statement in MySQL. It is a very useful clause when you are working with large datasets, as it allows you to return only the number of rows that you need, which can improve the performance of your queries and save valuable resources.

LIMIT Clause Syntax

The syntax of the LIMIT clause is very simple. You just need to add the keyword LIMIT followed by the number of rows you want to retrieve. For example, to retrieve the first 10 rows from the employees table, you can use the following query:

SELECT * FROM employees LIMIT 10;

LIMIT With OFFSET

You can also specify the starting point of the rows you want to retrieve by adding an offset value to the LIMIT clause. For example, to retrieve the 11th to the 20th rows from the employees table, you can use the following query:

SELECT * FROM employees LIMIT 10 OFFSET 10;

Use Cases For LIMIT

The LIMIT clause has a wide range of use cases, from simple pagination of data in a web application to complex reporting and analysis. Here are a few common use cases:

Pagination With LIMIT and OFFSET

One of the most common use cases of the LIMIT clause is pagination of data in a web application. You can use the LIMIT clause to retrieve a limited number of rows for each page and display them to the user. For example, to display the first 10 rows in the first page, the 11th to the 20th rows in the second page, and so on, you can use the following query:

SELECT * FROM employees LIMIT 10 OFFSET (10 * ($page_number - 1));

Reporting

Another common use case of the LIMIT clause is reporting and analysis. You can use the LIMIT clause to limit the number of rows returned in a report to a specific number of rows, for example, the top 10 or 20 rows based on a certain criteria. For example, to retrieve the top 10 employees with the highest salaries, you can use the following query:

SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

Debugging

The LIMIT clause can also be used for debugging purposes. You can use the LIMIT clause to retrieve a limited number of rows to test and debug your queries before executing them on the entire dataset. For example, to retrieve the first 5 rows from the employees table to test your query, you can use the following query:

SELECT * FROM employees LIMIT 5;

Conclusion

The LIMIT clause is a simple and powerful tool that allows you to limit the number of rows returned in a SELECT statement in MySQL. Whether you are paginating data in a web application, creating reports, or debugging your queries, the LIMIT clause is a useful and essential tool for working with large datasets in MySQL