Correct Use of MySQL LIMIT OFFSET Clauses: Limiting and Paginating

When you start working with a database management system such as MySQL, you inevitably reach the point when the amount of data grows significantly. It becomes hard to handle and use for both the developer and website or application.

MySQL has inbuilt functionalities for making the management easier. With MySQL LIMIT OFFSET clauses, you can control the number of records that are returned. In other words, when writing a specific query, you can use special clauses to limit MySQL data results to a specified number.

This set limitation helps you paginate the results, making it easier for the server to handle information. Using limit is highly recommended for handling large tables.

MySQL LIMIT OFFSET: Main Tips

• LIMIT is a special clause used to limit MySQL records a particular query can return.
• It can prove extremely useful if you want to paginate your query results, or manage queries on large tables. Returning significant amounts of data in those cases may affect performance.
• MySQL OFFSET is used to specify which row should be fetched first.

Usage of LIMIT

Imagine you are building an admin interface and you want to display a list of users. However, it seems endless. You'd like to paginate the query results to make it easier to navigate through.

To limit MySQL query results to just twenty on the first page, you can write a query using the SELECT statement:

$sql = "SELECT * FROM users LIMIT 20";

OFFSET Clause Explained

Say you'd like the second page to display the following twenty results. To do this, we have to use the the MySQL OFFSET clause. It will make the selection start from a specified entry. In our case, we will skip straight to the entry 21. Let's look at the statement below:

$sql = "SELECT * FROM users LIMIT 20 OFFSET 20";

There is also a way to make the statement shorter and achieve the same result. Note that in this case, we write the MySQL SELECT value first and the LIMIT value second:

$sql = "SELECT * FROM users LIMIT 20, 20";

MySQL LIMIT OFFSET: Summary

• When you only need a query to return a certain number of entries, you can use LIMIT clause to set that limitation.
• You can use it to paginate table rows or otherwise manage large amounts of information without disturbing the performance.
• If you want the query to return entries starting from a particular line, you can use OFFSET clause to tell it where it should start.