MySQL pagination

MySQL is one of the most popular database software and most loved as well by software engineers. I have used MySQL in tons of projects and have complete confidence in this software. I am sure if you have a software engineering background, you must have come across MySQL.

MySQL stores data in the database and table format and when the size of data grows it becomes a performance bottleneck to read all of the data. For few thousand records, it’s alright to run a SELECT * query and fetch all the records. It’s not recommended to do it for hundred thousand or million records.

Let’s consider a very simple data record, a user table that contains all the information about users. To fetch the record, you run this query.

SELECT * FROM users;

This returns all the records, you can filter them by providing a WHERE clause but the MySQL engine needs to scan all of the records to find the match.

To avoid this scenario, we use the pagination approach. In the pagination approach, we send back the information in pages or batches instead of the whole at once.

For instance, we send 100 records per page and the application can request more data if requires by asking for the next page data.

To achieve this in MySQL, we use the OFFSET and LIMIT clauses.

The OFFSET clause lets you skip the records and the LIMIT clause lets you limit the record to a certain number.

For example, if we want to read the 50 records per page. We run our query like this.

For page 1

SELECT * FROM users OFFSET 0 LIMIT 50;

For the next page i.e page 2, we tweak the query like this.

SELECT * FROM users OFFSET 50 LIMIT 50;

For the next page i.e page 3, we tweak the query like this.

SELECT * FROM users OFFSET 150 LIMIT 50;

And so on.

This article was first published on Shahid's blog.