Limit Offset is slow used for paging in MySQL/ MSSQL Server

In Mysql, we are using offset with a limit to use as paging to limit the data from the database but with a large limit of offset, the query performance will be down.

We can use the following possible solutions to increase the performance of paging SQL queries:

• First solution for paging: we always use order by clause for paging with limit and offset clause.

For faster access, we should use the Order by on clustered index column, it will give the better results. If you cannot use then atleast non-cluster index but cluster index will give you better result in large set like limit or offset value 250000 or more.

Note: Use column in order by clause having clustered index
-- Firstname and employeeid has clustered index
select * from hr.employees order by firstname,employeeid LIMIT 20,30;

Second Solution for paging: We can send the next page number for the next data instead of that we can send the last row of current data that is fetched and use the condition in SQL query to fetch the next rows data.

Example:
select * from hr.employees 
order by firstname, employeeid 
limit 20,30;

Second Solution:
--Use the last row data of current page to fetch next data by defining where condition --it will work faster for paging. In example john, 127 is last row.
select * from hr.employees 
where (firstname,employeeid)  > ('John',127)
order by firstname, employeeid 
limit 30;