The best way is to query only those rows that you need, and send only those rows back from the database to the front end.
MySQL's LIMIT M,N actually assembles the entire result set (imagine if there are a million rows!), but sends only N rows back. This less than optimal.
When you ask "Is there something like that in SQL?" you are presumably asking about Microsoft SQL Server, and the answer is no, it's even messier in SQL Server. Your first attempt to reproduce the M,N functionality might involve nesting a derived table, something like this:
select top N col1, col2, sortcol from ( select top M+N col1, col2, sortcol from yourtable order by sortcol desc ) as derivedtable order by sortcol asc
However, as you near the end of the table, you can see that this is quite inefficient.
Another solution involves keeping track of the value of the sort column from the last time the query was run, so that you don't need a derived table:
select top N col1, col2, sortcol from yourtable where sortcol > savedvalue order by sortcol asc
Here, savedvalue is the value of the last sortcol from the previous execution. Of course, you need to modify the query on the first execution.
Database pagination is not a simple problem, and will have many types of solution. When evaluating strategies, remember that you want to:
minimize query complexity and execution time
minimize the number of rows transferred back to the front end
These objectives often conflict with each other.
Dig Deeper on SQL Server Database Modeling and Design
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.