Hello there Wappler Team…
I’ve already implemented all my sub table and complex query functionality by writing MySQL views.
Would there be any benefit to using the sub tables and nested queries functionality for future database table structures?
Are views generally faster to execute than ST-NQ?
Would love to hear your thoughts!
Best wishes,
Antony.
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 queries you have. We have optimized that tp the maximum with sub queries and nested queries.
So it really depends on your specific view queries if they are faster.
Maybe you can show some of the view query code so we can check it out.
And you can do some speed measurements yourself by running you view and a similar sub table query that produces the same result.
I’ve used Views a lot for exporting Customized Data views into Excel Reports & Charts for corporate clients.
So, a less-than-perfectly optimized View only gets limited use “off to the side” of the main application. But the ability to customize those exports with a simple SELECT query of the View is a time saver.
I just read this article (from 2019) of the deeply convoluted process that each database server steps through as it explores “its own opinion” about how to process Views.
Thankfully, the latest MySQL version, properly installed and tuned, runs circles around the versions I had to use a decade, or more, ago.