Home / Questions / 18154
Accepted
dddenis
Asked: 2020-07-30 12:32:05 +0800 CST 2020-07-30 12:32:05 +0800 CST

What are the differences between Views and Temporary Tables in MySQL?

  • 772

What are the differences between Vistasand Tablas Temporalesin MySQL?

I have my doubts, after reading on the net about when I should use a View and a Temporary Table. In experience, I have always used temporary tables together with cursors but I had never touched views before and I see that the behavior is similar, where it is different and when it is advisable to use each one.

mysql

1 Answers

  1. Best Answer

    Regarding the first question, here are some important differences between temporary tables and views in MySQL:

    • Nature : A temporary table is a table, while a view is like a query or macro that is performed on other tables.
    • Persistence – Views are permanent and will continue to exist from session to session, while temporary tables will disappear at the end of the session.
    • Space : A temporary table requires additional space, while a view does not.
    • Integrity and Consistency : I had initially put them apart, but they are really related:
      • Some views can be updated and the values ​​that will be updated will be those of the source tables; instead in a temporary table, only the values ​​of the temporary table will be updated.
      • And vice versa: if the values ​​in the tables that originated the view are updated, the values ​​in the view will be updated; this is not the case in temporary tables.
    • Performance : views are not going to improve query performance, you can add indexes and keys to temporary tables to make them faster (although time is wasted filling them).
    • Locking - A query on a view can lock the source tables, while a temporary table will not.

    For the second part of the question: when to use each one? It will depend a lot on the use you want to make of the data contained:

    • If what you have is a complex query but without much cost in terms of performance, I would choose a view.
    • If the query is too large and complex and the data would not fit into the cache, I would use a temporary table.
    • If multiple operations and queries are going to be done on the data, I would use a temporary table.
    • If you want to manipulate the structure (eg add indexes to make queries faster), I would definitely use a temporary table.
    • If the data in the source tables is not going to change during the execution of your program (or you don't care), I would use a temporary table (it would avoid possible locks on the source tables).
    • 5