CODEWITHSUNDEEP
sqldatabasepagination
user5182503
user5182503

Reputation:

Pagination and alternative solution

Let's suppose I have a table with 10 000 rows. Some user (for example on web site) wants to work with this table applying some where conditions and order by conditions.

Variant #1 - using sql limit and usual pagination
As I understand database must 1)find all(!) rows that meet where conditions, 2) database must sort all(!) found rows at step 1 and from these found rows 3) it must return some set defined by limit and offset.

Variant #2 - reading all ids in list structure
Another solution is do a first query to get list of ids for certain where conditions and order by conditions and save it. For every page we get rows where id in {..} and sort on client side by list we have (because database will return rows in unsorted order). Advantage of this variant is not to filter and sort all rows for every page. Disadvantage is necessity to save rather long list. Besides this variant can be used only if data don't change frequently.

Is my understand of variant #1 right? Is variant #2 used in practice or it is bad solution?

Upvotes: 0

Views: 353

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Variant 1 is the "normal" way of approaching this. In many cases, you would be surprised at how well it works. Here are some reasons:

  • What looks like complex processing may actually be pretty fast, particularly by judicious use of indexes. (For instance, the sort may not be necessary.)
  • The results of the query may be cached between runs.

The second approach has some downsides:

  • If the underlying data changes, the ids may no longer be valid.
  • The entire result set (in terms of ids) needs to be generated up front. That can be expensive. I must admit, though, that if the query has an order by, this probably is no more expensive than the first run for Variant 1.
  • All the ids need to be returned to the application. This is the big killer, particularly if you can have really large numbers of ids being returned.
  • The application cannot continue processing until all the rows are returned, although there may be some ways around this.

That said, Variant 2 might be the best solution under some circumstances. However, the more typical solution is to let the database handle the pagination.

Upvotes: 0

Related Questions