EXPERT RESPONSE
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.
|
