How does pagination of results in databases work?

I desagree with @Bill Karwin. First of all, do not make assumptions in advance whether something will be quick or slow without taking measurements, and complicate the code in advance to download 12 pages at once and cache them because "it seems to me that it will be faster".

YAGNI principle - the programmer should not add functionality until deemed necessary.
Do it in the simplest way (ordinary pagination of one page), measure how it works on production, if it is slow, then try a different method, if the speed is satisfactory, leave it as it is.

From my own practice - an application that retrieves data from a table containing about 80,000 records, the main table is joined with 4-5 additional lookup tables, the whole query is paginated, about 25-30 records per page, about 2500-3000 pages in total. Database is Oracle 12c, there are indexes on a few columns, queries are generated by Hibernate. Measurements on production system at the server side show that an average time (median - 50% percentile) of retrieving one page is about 300 ms. 95% percentile is less than 800 ms - this means that 95% of requests for retrieving a single page is less that 800ms, when we add a transfer time from the server to the user and a rendering time of about 0.5-1 seconds, the total time is less than 2 seconds. That's enough, users are happy.

And some theory - see this answer to know what is purpose of Pagination pattern

Yes, the query is executed over again when you run it with a different OFFSET.

Yes, this is inefficient. Don't do that if you have a need to paginate through a large result set.

I'd suggest doing the query once, with a large LIMIT — enough for 10 or 12 pages. Then save the result in a cache. When the user wants to advance through several pages, then your application can fetch the 10-12 pages you saved in the cache and display the page the user wants to see. That is usually much faster than running the SQL query for each page.

This works well if, like most users, your user reads only a few pages and then changes their query.

Re your comment:

By cache I mean something like Memcached or Redis. A high-speed, in-memory key/value store.

MySQL views don't store anything, they're more like a macro that runs a predefined query for you.

Oracle supports materialized views, so that might work better, but querying the view would have the overhead of interpreting an SQL query.

A simpler in-memory cache should be much faster.