Is querying a single record from a MySQL view including thousands, equivalent to querying those thousands + 1?

Question

I have always hoped and assumed that it is not - that set theory (or something) provides a shortcut to the result.

I have created a non-updateable view that aggregates data from several tables, in a way that produces an exponential number of records. From this view, I query one record at a time. Because the underlying dataset is small, this technique works well - but I'm concerned it won't scale.

I've heard MySQL uses temporary tables to implement views. My heart lurches at the thought of potentially massive temp tables popping into and out of existence for each and every query.

Answer 1

Use explain <select query> syntax to see what really happens within your query.

Generally speaking, using a view is equivalent to using subquery with the same SQL. No better and no worse, just a shortcut to prevent writing the same subquery over and over again.

Sometimes you'll end up with temporary tables used to resolve some comples queries, but it shouldn't happen often if DB structure is well optimized and using views instead of subqueries won't change anything.