do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?


To answer your question literally, yes, in MySQL, views do exist as occupied space on the disk. But of course they do: if the didn't, where would they exist? If you rebooted your server, how would the views persist?

I imagine what you really meant was "do MySQL views occupy physical space in proportion to the number of rows they contain?", in which case the answer is no, they do not.

In MySQL, well:

Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

It's query that is stored, not the results.

The manual's section on view processing algorithms sheds more light on this: the normal method of handling a view is that the definition of the view is inlined into any query referring to the view (this is called the MERGE algorithm). In some cases, an alternative method is used, where the view's query is executed alone, and the results are stored in a temporary table, against which the referring query is then executed (this is called the TEMPTABLE algorithm).

The MERGE algorithm does not ever involve storing the results of the view on disk. The TEMPTABLE algorithm does, but only temporarily.

Views work the same way in other databases. This is how they work in PostgreSQL, for example.

However, some databases support another approach as well: materialised views. With these, the view's query is executed ahead of time, and the results are stored on disk. Materialised views views do indeed occupy physical space in proportion to the number of rows they contain. This consumes more space, requires work to be done ahead of time, and introduces a lot of complexity (shared by the user and the database to varying extents) around making sure the view is up-to-date, but offers potentially much faster queries, because the work involved in the view's query has already been done. Oracle supports materialised views, and has for a long time. PostgreSQL currently does not, but will do as of the next version (which will be 9.3). In databases without built-in support for materialised views, there are ways to emulate them using triggers.

| improve this answer | |
  • 2
    +1, but wish to stress on the TEMPTABLE algorithm: yes, when using TEMPTABLE you will temporarily consume space (memory or disk); but this does not take up on "view space", but rather on "query space", or, to be more specific, connection memory. So this memory has to do with number and type of queries rather than number or type of views. – Shlomi Noach Apr 15 '13 at 6:21

Very interesting question!! Due to the fact that queries are re-executed in MySQL when you're selecting from a view, and considered to be a subselect at execution time. I believe PostgreSQL handles this differently, but my answer would be no, it doesn't take up disk space besides the data necessary to store the query that builds the actual view

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.