0
<table 1>
<table 2>
<table 3>

<view 1>

If view one is a complex query of the tables 1 -> 3... when is view 1 updated?

Is the view updated when any of the tables that make the view are updated, or is the view running the query each time a request to the view is made?

From the tutorials the views reflect the changes in the tables, but what is the underlying logic. I want to know if it is more effective to use the views as a kind of mem caching or if i am way off topic...

  • 1
    The view is not really "updated". It shows you the results from the 3 tables at the moment it is executed. In other words: the data from a view don't exist physically at all times, they are retrieved when the view's query is executed. – Thomas G Nov 18 '16 at 14:08
  • 1
    Although old, [How do MySQL views work?](http://stackoverflow.com/questions/2878227/how-do-mysql-views-work) is still correct. You could think of a view as a way to abbreviate code, the data is not actually stored somewhere (so it doesn't get old), but the code of the view is inserted where you use it, and it will get included into the optimizing process as if you had written it there. If you use something like e.g. `select * from myview where 1=2`, it will not first "update/fill a view" and then look for rows that fit, but it will just not do anything with it (well, unless you force it). – Solarflare Nov 18 '16 at 14:11
  • @Solarflare you could have made an answer out of this ;-) – Thomas G Nov 18 '16 at 14:14
  • right ok.. so this is not really the way to go in my case as every time a user calls a route that would request a result from this view the database query would be run. Quite different from a memcaching system... i was hoping the mysql view would somehow cache the result and only update the said cache when one of the tables was updated. –  Nov 18 '16 at 14:14
  • 1
    A Materialized View would be suitable in your case, unfortunately this mechanism doesnt exists in MySQL. But you can mimick it with a scheduled procedure creating temp tables. – Thomas G Nov 18 '16 at 14:16

0 Answers0