Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from stackoverflow.com Apr 14 '13 at 3:10

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
1  
+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
add comment

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

share|improve this answer
add comment

Yes View also need space to store it. View is also similar to table created using required data. Views basically use for data fetched from complex query and to use frequently.

share|improve this answer
    
You better read answers above. Many people think exactly the same way... –  leonardo_assumpcao Apr 13 '13 at 18:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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