MySQL LIMIT and OFFSET Clauses: ResultSet Pagination

In this article, you'll learn what pagination is and how to divide your data sets into chunks for better page speed and optimization, and user experiences using MySQL's LIMIT and OFFSET clauses.

What is Pagination?

Many websites contain large amounts of data with the goal of delivering that data to the end-user as quickly as possible. Loading and displaying hundreds or even thousands of records at once is poor design and will quickly lead to a bad user experience consisting of large pages and long load times.

Pagination divides that data into equally sized chunks, allowing to pull and display only portions of data at a time. You see it every day when you're browsing the internet. Google search results are a prime example, defaulting to ten results per page with the ability to navigate back and forth between the different pages of results quickly.

Other examples include many e-commerce websites with product listings and directory websites. Even social media apps use pagination to display the posts within your feed.

How to Utilize Pagination with MySQL

MySQL's LIMIT clause is pretty simple.  You're essentially limiting the number of records returned in your query's resultset to a set value.

For example, say you have an extensive listing of products on your website. You want to add a search option for your customers to find specific products based on their search terms quickly. If you have thousands of products to filter through, you're not going to want to list all of them on a single page. Doing so could affect your page speed and user experience negatively, as discussed previously.

Using the LIMIT clause in MySQL is simple and only takes a few lines of code. Here's a sample of what that search query could look like including implementing the search term as well as pagination:

SELECT	id,
		title,
		price
FROM	products
WHERE	title LIKE '%[search_term]%'
ORDER BY id ASC
LIMIT 0, 10;

Here, we're selecting a few columns from our hypothetical products table that we'll use to display on our search results page. The piece we're concerned about right now is how we've used the LIMIT clause. Generally, you would limit record counts using one numeric value. With pagination, we're using two:

• Offset value (0): The starting record in the resultset.
• Limit value (10): The number of records to return.

This query will return ten items from the first page of the resultset. Increasing the offset value to 10 will return the second set of ten items. Increasing to 20 will return the third set of ten items, and so forth.

Here's a visual example of how it works. The first resultset with the offset value set to 0 returns the first set or page of items:

And the second resultset with the offset value set to 10 returns the second set of items:

The first record returned in your resultset will always have an index of 0, so your first offset value should also being with this value.

Also notice that the offset value increases by the same amount as the limit value. Using any other value would skip over records in your resultset. Meaning potential customers on your website searching for products would never see them in the search results because they weren't returned.

Here's a real-world example of how an offset calculation works to determine the page number to return. Say you have a function that accepts a parameter page. Remember the default offset value of 0 is the first record, so our calculation could be as simple as this:

limit = 10;
offset = page * limit;

• The record limit is preset to 10 for each page.
• The offset is then calculated by multiplying the referenced page parameter value by the limit, returning 0, 10, 20, 30, etc. in sequence.

LIMIT with the OFFSET Clause

Another very similar way you could format how this code looks in MySQL is to remove the comma-separated list of offset and limit values and use the OFFSET clause in its place. So instead of writing the LIMIT clause in MySQL like this:

LIMIT 20, 10

You could quickly rewrite it to this:

LIMIT 10 OFFSET 20

Both return the same results with no difference in response time.

Conclusion

There's a lot of explanation here for such a simple process. I wanted to present every possible angle to help you fully understand how MySQL limits, offsets, and pagination work. Hope that helps. Happy coding!