...
We will focus on item 3 here
The design pattern here is to implement an intermediate indexing scheme which is limited at the system level Overview of approach
provide a system level implementation of “system paging” for indexes suffering issues
We will create a lookup table which will have an internal page number for each of these multitude of records in the problem table. in order to migrate to using this, we must populate the lookup table (so there will be one record in the lookup table for each of the multitude of records each record with a page number which groups the records into a set of manageable records per page). The page size must be limited to some reasonable number of records (we recommend 500 as a first guess). so we will “group” troubled items indexes into groups of size 500, so now they can be looked up using paging without ever having an issue. perhaps as few as 300) within the multitude of records.
To read data using the lookup table, We will first determine the pages of interest using the offset and limit passed in, we will then query the lookup table for items in each page. We will get a page of records at a time, then we will get these records from the problematic table by their primary index and return them in the results. This enables the use of offset and limit without experiencing the issues we have with our present implementation of paging in the FIO getters. the result is that limit and offset should ALWAYS work as expected, no matter the number of records associated with a given index.
one lookup table can be made per problematic table, or one table can be made to be used FIO wide for all tables.
it is recommended that we prototype this to gain confidence that the proposed solution will work robustly for any number of records contained in a secondary index.
Detailed example
We will integrate the lookup table for the desired tables everywhere a record is inserted or removed.
...