How to display 50 necessary records from the database to millions?

Question

Python

async with request.app['db'].acquire() as conn:
            result = await conn.execute(
                select([db.my, ]).where(db.my.c.date == date)
            )
            a = await result.fetchall()

Once asked the question "How to request 20 rows from the database?" and I heard the answer that you can use offset and limit, but the point is that if you have 50k records there, then it’s normal, but when there are more than a million there are strong delays. You won't be able to get it by id, since there is still sorting by different parameters.
How can you most efficiently get 50 necessary records [0:50] ?

Answer 1

In general, in terms of speed, Limit is no different (well, almost) from a full SELECT. In your case sorting brakes. Try to index the field by which sorting will be carried out.

Answer 2

No way, if you immediately need data from the 100500th query line, there are cursors for the rest