Searches

One aspect that often contributes to consumption is the use of database searches via the . These operations, while of course necessary for retrieving and manipulating data, can be resource-intensive, especially when dealing with large datasets or complex queries.

Particularly complicated database searches can also slow down the query itself. In some cases, this can be noticeable to your users, and can be detrimental to your app's user experience. Sometimes, complex queries are needed – it's simply a part of the work your app is doing and cannot be avoided – other times they can be tweaked to be more efficient or even removed altogether.

In this article series we'll dive into some of the different aspects of searches and how the affect .

What is a database search?

If you are new to databases, we have an entire section dedicated to the database is and what it does. We recommend reading this first to know the essentials if you are unfamiliar with this part of Bubble.

Article series: The database

Let's first look at what a database search is from a WU perspective. Database searches are a data source, meaning that they are a place from which Bubble can pull data to use in your app, such as populating a repeating group or table element.

Dynamic expressions and WU

Database searches are built using dynamic expressions. They are highly flexible, giving you as the developer the freedom to set them up to serve almost any purpose – but that flexibility also means that you are free to set up searches that are not optimized for performance and WU consumption. This also means that we can't give a one-time answer to exactly how much a search is going to consume, for two reasons:

• The volume of data in your database affects the consumption. More data means more to search through, giving the server more work to do.

• The complexity of the dynamic expressions can also increase the server load

How it is calculated

Earlier in this series, we described the theme park entry ticket metaphor. The gives you an overview of the entry ticket cost of performing a search, and from there, additional work that you give the server is added to that base cost.

For a search, you are in most cases consuming WU from the following activities (base cost) along with any extra server work specified in the dynamic expression:

• Starting the Do a search for WU activity (base cost per search)

  • Complexity of the search as specified in the dynamic expression (added cost)

  • Volume of data to search through (added cost)

• Sending the data to the user's device (base cost per character)

What is database volume?

In simple terms, when we say database volume, we are talking about how much data Bubble has to search through to find the you are looking for. The database volume is affected by three things:

• The number of things saved in your database

• The number of custom fields on the data type you are searching through

• The volume of data stored in each thing (for example, a blog post or long product description will add more data to your database than a simple first name or phone number)

It can be helpful to think of the database as a spreadsheet such as in Microsoft Excel. It consists of rows and columns and is saved as a file. Each row and column is usually insignificantly small in isolation, but as you add more of them, they can add up to a significant file size, and can be more intensive for the computer to search through – whether it's an Excel file on your own device or a database stored on a remote server. This is true for any app that works with a database, and not a challenge for Bubble in particular.

With that in mind, you can make database design decisions that keep your database as lightweight as possible. In that process, we of course encourage keeping the user experience as a major counterweight. While a "heavy" database can mean more WU consumption, storing data is what the database is for. As always, it's about using efficient methods to find the right balance between efficiency and functionality.

What is search complexity?

The second part of the equation is the complexity of the dynamic expression that makes up the search. To understand what makes a search complex, we need to look briefly into how a search is actually performed.

The process of elimination

In simplistic terms, a database search works in part by eliminating records that don't match your constraints. Let's say, for example, that your database consists of 100,000 things, and 80,000 of them don't match a specific constraint, Bubble can remove those 80,000 from the potential "candidates", thus vastly decreasing the final amount of data that has to be searched through to find the right thing(s).

This leads to a conclusion that may feel counterintuitive: the more constraints you provide, the more efficiently Bubble can perform the search. As such, more constraints do not necessarily add to the complexity of the search – quite the opposite. This is especially true for large data sets where Bubble works in the background to index the database, meaning that we make the process of elimination faster and more resource-friendly by optimizing it for common queries.

Adding more constraints to a search can make it less complex, since it helps Bubble eliminate things from the list.

What does make searches more complex is when you start adding advanced filters that forces Bubble to do processing on each row of the data. Take a look at the expression below:

The :filtered operator can sometimes drastically decrease the efficiency of a search, both slowing it down and increasing WU consumption. Click screenshot to enlarge.

In the example above, it's easy to think that you are performing one search, but actually, we are potentially performing one search per row of the results of the first search. The expression in the screenshot is searching for products that match the average price of all products within the same category.

First, the Do a search for matches 1,000 products, and then Bubble applies the filtering: the result is that Bubble is not performing one search, but potentially 1,001 searches: a drastic increase that would consume a significant amount of workload and slow the search down.

What could we do instead?

• In general, we recommend that you try to avoid using the :filtered operator, as they can be fairly taxing on the server.

• Instead of performing a search every time we want to know a Category's average price, we could save the average price on each Category in a number field. This way, we could set up a dynamic expression that checks a This Product's Categories' Average price (see screenshot below).

• Consider whether there are even simpler ways to set up a search with similar results, and if the search is "nice to have" or necessary.

• You can also save the results of the search as a list, to avoid having to perform the query every time a user requests it. For this method, you'll need to develop your app to keep the list up to date, as lists don't automatically update. Also, lists can hold a maximum of 10,000 things.

By storing the average price in a separate number field on the Category data type, we can avoid performing as many searches as in the first example.

The example above still requires Bubble to check the price of each Product in the search against its category, but we avoid performing multiple searches to calculate the average.

This would require you to keep the average price on a Category up to date. You can ensure this by using whenever a Product's price changes for example.

Sending the data

When a search is complete, Bubble will send the expected data to the user's device. The amount of data being sent can vary dramatically, depending on how much data the data type contains (the "blog post" vs "first name" example), how many things to send and any operators that you add to the search.

For example, downloading a list of 1,000 blog posts to a repeating group (even if you are only displaying the title of the post) can mean that Bubble has to send a significant amount of data, whereas if you instead use an aggregation operator such as :count or :average, Bubble only returns a number, which is a fairly insignificant amount of data almost regardless of the number.

It's important to be aware that Bubble sends all data saved on a thing when a thing is queried, and not just those that are displayed on-screen. The exception is fields that are protected with that the current user does not match – that data never leaves the server.

Knowing this, we can tailor our database to download less data:

• Set up privacy rules that protect specific fields from being downloaded. This secures the information, and ensures that Bubble doesn't send more data than what is needed.

• Consider what data is needed on your data type. Sometimes fields are added that do not add to the overall user experience or general quality of the app

• Also consider the number of things you need to download. You can avoid downloading too much data by setting up repeating groups that load as the user scrolls, use pagination or simply reduce the number of things overall.

• In some cases, it makes sense to split data types into separate types where one is specialized for search results, and the other contains the data. This is often referred to as a satellite data type in the Bubble community.

As you plan your database, we again encourage you to not put emphasis only on WU. The cost of sending data is very low, and you should balance the optimization process with deadlines, app complexity and user experience.

FAQ: Database searches and WU

If I perform an identical search multiple times on the same page, do they all add to WU?

Bubble is set up to not process a search query more than one time. In other words, if you perform the same identical search in two different places on the same page, the search will only be performed once. If the queries are not identical (such as one having an extra constraint), they will be processed separately.

If a search is performed, and then a change is made in the database that updates the results, is the query performed all over again?

The results from a database search is dynamic in the sense that any change made to the database by any user is immediately visible in the app of all other users. However, this doesn't mean that the entire query has to be re-processed. Bubble only sends the necessary updated data, making the process very ligthweight.

If you'd still like to avoid this, you can use the : operator to save the list without the dynamic connection to the database. While there are scenarios where this approach makes sense, we'd generally discourage using this to save WU, as it can lead to data inconsistencies in your app.

If I use the "Current user" data source, will it consume WU?

Any information on the Current user is downloaded when the page loads, regardless of whether you reference this anywhere on the page. This includes all fields saved on that user, except for those that the current user is not allowed to see as a result of privacy rules.

If a Make changes to a thing has a condition, which uses a search that is used in the make changes action itself too, will we be charged for search twice?

Generally, Bubble doesn't perform a search more than once, as longs as they are identical. This applies to workflows as well as in elements.

If I search for data and load it into a repeating group on one page, will Bubble cache the result so that the query doesn't need to be performed a second time when I navigate to a second page?

Bubble searches are dynamic, and always kept up to date with new information in the database. As such, every time Do a search for is used on a new page, the query will be processed as a new search. This also applies if the current page is refreshed.

Does Bubble perform indexing on the database to make searches as efficient as possible?

Yes, with some caveats. Database indexing can greatly improve the efficiency of a search, but only when the database volume is large enough. Bubble processes different data types and queries intelligently to index the database as needed. This can sometimes lead to varying search speed and WU consumption at different times, depending on whether the database has been indexed for a specific query.