The situation: I have a million records on a student table.
I wanna query the data from this table.
Condition: The student verified it and scored more than 70 points with many conditions and operators.
I wanna get 100 records
In the first request limit of 1000 records, I got only 5 or 10 students valid with my condition.
So i have to loop using LastEvaluatedKey and get more limit(numberLimit) until i earn 100 record that are valid based on my query condition
I would like to know how many numberLimit I should query in the next query.
And the worst case i did, i had to search all tables that had about 8K records, and they spent more than 87.03s
Pick a page size that is appropriate for your data. For example, if you need 100 items and 95% of your data matches the criteria then set the limit to 110. If you still don't meet 100, see how many you are short and search for a few more than that.
But as you've witnessed, filtering data is not efficient, it's difficult to know how many round trips it could be. For that reason it's probably best to create an index which has the filter criteria as part of the key. That way you can obtain 100 items in a single convenient request.