Views with UNION ALL
I have scoured the SQLite website, but I am not able to find a reference that answers the question below. If I have missed it, and you can point me to it, that would be great. Thank you.
Consider that I have a view ViewA. Now, I use ViewA in a UNION query as below:
select something from ViewA UNION ALL select somethingelse from ViewA UNION ALL select athirdthing from ViewA UNION ALL . . .
My question is: is the query for ViewA run multiple times in the above query or is ViewA computed once, stored in memory and used for all the queries in the compound query without being run multiple times?
I don't think any amount of scouring will reveal a commitment to any particular query plans for particular queries.
There is an easy way to find out what will be done for a query in the shell. Enter:
EXPLAIN SELECT ... ;
, then examine the output.
There are many different ways that the query could be run. The query planner looks at lots of possibilities and tries to pick the one that will run the fastest. Whether or not this runs ViewA once and caches the result or if it runs ViewA multiple times, is indeterminate. The answer can vary from one version of SQLite to the next, from one schema to the next, or between two consecutive runs of the same query.
Thank you. That makes sense. I guess the question is how reliable SQLite is in being able to estimate what would be faster. I thought it would always be faster to cache the results of the view, but I guess not. I will look at the documentation on EXPLAIN and see what I can make of it. Thanks again.