Created view loads every time I want to see the contents

Question

I got a weird situation where each time I want to see the contents of a generated table view it starts loading the data instead of immediately showing the data.

When I use this query the data will be showed immediately:

CREATE VIEW train_set
AS SELECT *
FROM logs where ((logs.timestamp >= '2014-03-01 00:00:00') and (logs.timestamp < '2014-03-16 00:00:00'));

But when I use this query:

CREATE VIEW train_set
AS SELECT *
FROM logs where ((logs.timestamp >= '2014-03-01 00:00:00') and (logs.timestamp < '2014-03-16 00:00:00') and logs.user_id = '10');

for each time I want to see the contents of the Table View: train_set it starts loading like it's actually executing the query again.

Does anyone now why this is? I am using Sequel Pro on my Macbook

Answer 1

It IS executing your query every time you open the view. Thats the point of the view. To get a middle-step in between of your table and the results you want to grab. Or to get a fast view into the database for relevant data (like daily entries).

The only explanation for me is that your first query is just faster so you can't see it loading. But in fact, both are getting executed on every show-up.

I cant imagine why someone would want to create a view which is executed only one time (at creation). Alternativly for this you could just export your stuff to an excel file. Wouldnt that be the same?

Answer 2

Well, generally views are refreshed on select. They are not refreshed instantly because they fetch all new rows in the "viewed" table when you activate the trigger (select or anything else).

Lets get started with another concept which'll help, "Materialized Views". Mysql by default can't use this feature without some manual work.

First of all, a Materialized view is a "stored view" (technically speaking it's not, but for the sake of explanation, it is). So then, what do you need to do?

Create a new table called "train_set_mv",

CREATE TABLE TRAIN_SET_MV AS SELECT * FROM LOGS 

^{(A simple way to make an image, create all indexes to performance because it's about log)}

Now create a trigger in your base table after INSERT, DELETE, UPDATE, etc., like the following:

DELIMITER |
CREATE TRIGGER trig_logs AFTER INSERT ON logs
   FOR EACH ROW BEGIN
      INSERT INTO train_set_mv
         SELECT fields
         FROM logs log WHERE LOG.ID = NEW.ID; 
   END;

You see, Views are just images that refresh every time you pull the trigger. You need to put it into a table every time the log changes. Remember, you'll need to include your users' ID, time stamp or anything else you want.