do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com Apr 14 '13 at 3:10
This question came from our site for professional and enthusiast programmers.
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:
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
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.
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
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.