Performance of Searching with Lists as a Constraint vs. Filtering

Need help Database
1

I have an interesting question regarding the hypothetical performance of an app design choice.

Premise:

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.

The 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 Approach:

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.

The Question

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.

Thank you,

Austin

3 Likes
2

I bump this!

3

Interesting question. You may have solved it already, but a couple of thoughts:

  • Having an "Item"and “Items” fields so that you can link a single item or multiple items seems like overkill and a nightmare to keep on top of. Just have “Items” and that takes care of it since you can link single or multiple using it.

  • With my app, I have several instances where I’m searching a datatype based on a list field within that datatype. For example if a datatype of Blog posts had a list field of “Tag” where you could link each blog post to its various subject Tags, the search constraint is something like… Tag contains [input field name] Tag’s value. This search is done server side by Bubble and is fast (I have close to 10,000 entries currently).

  • The problems only arise if you want to search for multiple Tags at once. This is searching a list based on a list, and the only way I’ve seen it done is through an advanced filter and the “intersect with” function. This is done client side and is much slower, though I’ve gotten it to work fast enough for my purposes by optimizing other things. Note that you can also do this search as an “and” or an “or”.

  • I’m not following the issue with users deleting items and problems that causes. I’d think that if there no items linked to something, then nothing would display, which would be accurate. But if you don’t want to display activities that have no items, then you could set a constraint so that the Items field has to not be empty.

1 Like
4

This is very helpful @ed727. Thanks for sharing.

5

7 months later, I’m revisiting this. I don’t know the official answer, but from feeling out other apps, it seems like @ed727 's approach of just having one field of type item list would be sufficient for speed. Another possible way, if you anticipate orphans being uncommon, would be to have a group encompassing the entire cell be hidden if its content is empty, and its height collapse.

Though it should also be noted, its probably better to remove orphans as items are deleted via API workflow, since viewing an activity feed is a much more common action than deleting content for an activity, thus should be prioritized in terms of speed.