Why is pagination so expensive?

Question

This is one of those things that seems to have an odd curve where, the more I think about it, the more it makes sense. To a certain extent, of course. And then it makes no sense to me at all.

Mention me?

Answer 1

Because in most cases you need to sort the results first. For example, when you search on Google you can only see up to 100 pages of results . They don’t worry about sorting by page over 1000 sites for a given keyword (or a combination of keywords).

Page breaking is fast. Sorting is slow.

Answer 2

Lubos is right, the problem is not that you are paging (which takes a HUGE amount of data from the wire), but that you need what actually happens on the page ..

The fact that you need a page means there is a lot of data. A lot of data takes a long time to sort :)

Answer 3

This is a very vague question. We need a concrete example to better understand the problem.

Answer 4

This question seems pretty well covered, but I will add a little something specific to MySQL as it catches a lot of people:

Avoid using SQL_CALC_FOUND_ROWS . If the data set is not trivial, counting matches and getting x the number of matches in two separate queries will be much faster. (If this is trivial, you are unlikely to notice the difference anyway.)

Answer 5

I thought you meant the pagination of the printed page - where I cut my teeth. I was going to go into a big monologue about collecting all the content for a page, positioning (there are a lot of rules, powerful engines) and an excuse ... but, apparently, you talked about the process of organizing information on web pages,

For this, I would suggest that it gets into the database. The drive is slow. Once you have acquired it in memory, sorting will be cheap.

Answer 6

Of course, sorting by random query takes some time, but if you have problems regularly using the same requested query, there is something wrong with setting up the database (incorrect indexing / no at all, too little memory, etc. .). I am not a db manager), or you are doing pagination incorrectly:

Terribly wrong: for example. making select * from hugetable where somecondition; into the array, getting the page counter using array.length, select the appropriate indexes and dicard array, and then repeat this for each page ... This is what I call serious wrong.

The best solution for two queries: one gets only the score, and then gets the results using limit and offset . (There may be one query option on some patented, non-standard SQL server, I don’t know)

A bad solution can actually work on small tables (in fact, it is inconceivable that it is faster on very small tables, because the overhead of executing two queries is more than getting all the rows in one query. Saying that it is .. .), but as the database begins to grow, the problems become apparent.