Subtable query under the hood

[George]

@Mr.Rubi

it turned out that the most productive solution is the standard Wappler queries with subtables. This outstripped even MongoDB queries in speed, where documents with the entire complex structure were stored in the database. It is also faster than custom queries with aggregation to obtain a complex structure of documents.

Great to hear, yes sub queries are indeed very optimized and do merging of data on the fly.

See more details on:

Sub Tables and Nested Queries Versus Views

Well sub tables or just related one-to-many tables that you probably already have. And Multi References are Junctions Tables for Many-to-many relations. Wappler’s Database query builder is optimized so it does the most optimal sub query to get the nested data only for the selected parents with minimal number of queries. MySQL Views are just predefined queries. The speed of queries depends on the number of indexes and how well data is linked. So the speed all depends on the type of data and q…

Joins vs Sub Tables

Sub tables are just like any other tables with relation to a parent table. You probably want to compare joins agains nested queries with sub tables. The difference between those two is the amount of data and the structure. There isn’t much of performance difference. A regular query with a join to a sub table will actually produce a lot of data replicating the parent records to all sub table records and you will need to filter or group this data later on. It is done in a single query. While a…

I’m sure @patrick can also provide some insights