Can someone explain how to use search_after for more than 100k records without fetching 10k records just to get the sort value of the last record just to get the next 10k records?
Here's an example:
I have 100 100 records. I want to get the last 100 records. I have index.max_result_window set to 10 000 (default value).
- How to fetch the last 100 records, without fetching the first 10k and the the next 10k, etc. using the
sortvalue of the last record of each 10k records and placing it into thesearch_afterproperty just to fetch the next 10k records which I wont use? - How to fetch 100 records in the middle (e.g. 86 700 - 86 800) without fetching 10k records at a time (which again, is waste of time and resources)?
- If I have the last 100 records, how to get the previous 100 records (from 90 900 to 100 000) and then the previous 100, etc.?
My real case is that I have a table with pagination. The user can adjust the page number and can jump between pages from the available page buttons (10 at a time). It can also jump to last, first and random page. The issues are: (1) if the user click the last page button, (2) and then click the previous page button, (3) then click a page button that is in the previous 10k records threshold, but not necessarily the previous page of the current page, (4) go to a random page.