The creation of the filter is a very expensive operation, as it needs to fetch all possible results to create the filter.
Any change in a table will invalidate the filter and will make a new creation necessary.
We should implement a logic to recreate the filter tight after the table cache was purged, independent of an actual user requesting the specific table.
The way to go would be:
[ ] When calling a filter creating sql, save this sql in a separate cache
[ ] When purging cache, get this sql from the separate cache
[ ] Add ad hoc task to be run immediately with the command to execute sql and, in the process, recreate filter
[ ] To make sure we don't waste resources, there must be a short (or even better a variable and potentially increasing) delay to make sure we don't get hit by a period of very fast turning caches (eg: uploading a lot of new records where after every record the cache is purged)
This will not take care of a "purge all caches" situation, but that is not what usually happens. We just want to improve performance for users on fairly (but not very) often turning table caches.
The creation of the filter is a very expensive operation, as it needs to fetch all possible results to create the filter. Any change in a table will invalidate the filter and will make a new creation necessary.
We should implement a logic to recreate the filter tight after the table cache was purged, independent of an actual user requesting the specific table.
The way to go would be:
This will not take care of a "purge all caches" situation, but that is not what usually happens. We just want to improve performance for users on fairly (but not very) often turning table caches.