I have an interesting question regarding the hypothetical performance of an app design choice.
I am creating a feed-like system where the user is shown the most recent "Activity"s (thing) on a page using a repeating group. The repeating group’s data source is “Do a Search For Activities-> sort by Modified Date (descending)”. Each Activity has a Type (text) field drawn from an option set which represents the type of activity. Each Activity also can have either 1 or multiple attached "Item"s (thing), depending on the activities type.
My initial thought to do this would be to have two fields, Item (Item) and Items (List of Items) which are populated based on type of activity. However, the scenario of cleaning up orphan Activities occurred to me. If a user deletes an Item that an Activity references, we don’t want blank entries being shown in the feed. Orphans can either be cleaned up on deletion or just not shown in the feed group, but both solutions pose the same problem.
Essentially, the search has to be something like “Do a Search for Activities where Item is X OR Items contain X”. In the case of cleaning up orphans, X is the Item to be deleted. In the case of just not showing empty Activities, X would represent “not empty”. However, the above search is impossible, because in bubble, like many databases, you cannot perform a compound search with “OR”. Thus in order to obtain the data, you actually need to apply an advanced filter after the search, which, as has been pointed out in other threads, can cause performance issues on large numbers of items.
Another idea that solves this issue would be to remove the “Item” field completely, and have single-Item Activities just utilize the Items field with only one Item in the list. This would mean the above search could be done without an OR via a “Items contains X” constraint. HOWEVER, this also means that all searches for single Items would require doing an “Items contains X” search. I’m unsure of how bubble’s database is formatted behind the curtain, but I know that often in databases lists can’t be indexed like other fields can, so this could theoretically slow searches down significantly.
Which approach would result in faster searches at higher volume of items? Would either approach (remove orphans or ignore them) result in better overall performance for the user? A basic assumption should be that users are viewing the feed much more than they are deleting Items.
@josh I saw you had posted a thread a couple years ago detailing performance of searching, so perhaps this would be up your alley.