There are several best practices when it comes to optimizing your Database's speed and performance. This is an iterative process but will become important especially as you start to work with larger data sets of over 10,000 records.
There are three variables when it comes to database performance:
- 1.Database Model Design
- 2.Querying Data Efficiently
- 3.Setting up Indexes
A database model is the logical structure of a database, including its relationships and any constraints that determine how data can be stored and accessed. Designing your Database Model in the "right" way for your specific application will make a big difference in how performant your app will be. This is really hard to get right the first time and usually takes a few iterations, but some best practices might help you get off to the right start.
This usually goes hand in hand with efficient Database design, but if you've modeled your Database in the most performant way possible, the next thing to do is look at the way you query data. This usually involves using Addons where possible to enrich your API response with related data rather than trying to get it recursively or with joins.
Indexes are a powerful tool used to speed up querying data. You can think of an index similar to a table of contents "index" in the back of a book. When you want to quickly look up something, you refer to the index and jump to the page. Xano automatically indexes your data in the most performant way it knows how, but if you want to provide a specific pointer to data that's important to you in the table, you would define a new Index.