Creating Views & Inserting Data

[00:00:00]
>> Let's talk about Views. We're gonna come back to indexes a little bit later. And we're gonna do a bit of a magic trick. I'm gonna show you how to speed up a query by 4,000,000%, which I'm pretty excited to do it. It's the crown jewel of this course.

[00:00:18]
But we have to work up to it. It's gonna be the combination of a lot of concepts. First, let's talk about views. Let's go back to English category names, we talked a little bit about that, right? Let's fathom for a moment. Again, we have this website, we have that we're mostly showing English category names, and we don't need to show French, Italian, German.

[00:00:43]
We could, every single time, do this query of SELECT category_id, name, language. FROM category_names WHERE language = 'en'. Right, and we can do this literally every single time that we need to populate this database Any time that we have to do any joins, we, By the way, I don't vet anything that goes into this that shows up.

[00:01:19]
So if something weird shows up as like a category name, I swear to God ,I didn't put it there. I was worried for a second that I saw that something is gonna be bad. So far so bad, for-
>> Number 17 looks like German. [LAUGH]
>> It probably is.

[00:01:32]
>> [LAUGH]
>> So the data integrity here is not the best either, but it was free, which was the correct price. So this is burdensome to have to do this every single time. And anytime that I wanna do a join, I have to join this big, massive thing and I have to know where everything is coming from.

[00:01:54]
I don't like it, I wish it was easier. It'd be nice if there was just a table that had just the English names on it, right? And I didn't have to do any sort of fun join to get that. But turns out, there's a way to do that.

[00:02:12]
And it's as easy as I'm just gonna say in front of the SELECT statement. I'm just gonna say CREATE VIEW english_category_names as blah, as query. Now, if you look at my tables here, you can see actually have a view as an entry here. And it looks just like a table.

[00:02:45]
It has a category name, a link and a language. So now I can just say SELECT * FROM english_category_names And you know what I did that I should not have done?
>> Put the LIMIT 5.
>> I put the limit on there. So it's actually only gonna get the first five ever.

[00:03:04]
So let's actually DROP VIEW english_category_names, and we're gonna recreate that. You could alter the query as well, but we're not gonna do that. But now we can do that and say, LIMIT 5, or LIMIT 15 so that we get more, right? This is now a trillion times easier for us to deal with, Right, we don't have to do any expensive joins or anything like that, we can just get, it's basically a lens.

[00:03:42]
If you're familiar with the programming concept of lens, there's some languages that are like really big into it. It's a way of looking at data, but it's derived and can alter the data as it goes through a lens, right? Same idea applies here. If you're not dealing with lenses, then just ignore everything I just said.

[00:03:57]
I showed you a really easy one more, it's just like aware. But these can be big complicated joins as well. It doesn't have to just be a simple WHERE, it can be literally any SELECT that you can think of. It's useful for convenience, it's also useful for permissions.

[00:04:13]
So imagine you have a contractor working with you. And you have some part of the database that you need to show the contractor, and they need to be able to SELECT FROM, and you can't show them all of the database. You could create a view, and then you could give them access to just the view.

[00:04:28]
From their perspective, it's a table they can do whatever they want from it, but they wouldn't be able to access anything else in the table. So from a securities and permissions kind of point of view, it's also a useful thing. Another interesting and useful thing is I can actually INSERT INTO a view.

[00:04:49]
Category_id, name, language, VALUES 2, We should definitely have a topic about Brian Holt, right? Absolutely, and just to prove a point too, despite the fact that this table only shows English, it's not gonna verify that as it goes through. So I'm gonna put this in as an Italian keyword.

[00:05:22]
So actually, I wouldn't be able to select this back out of it because it's an English table, but I was able to insert into it Italian. Again, it's not verifying the WHERE clause there. But because there's a one to one mapping of the view to the table, it's just like, okay, you're inserting into this, you're obviously inserting into the underlying table.

[00:05:44]
>> Are you inserting a second thing with category ID 2 now?
>> So SELECT * FROM category_names WHERE id = 2. Or what is it? Category_id. So in English its source, but in Italian [FOREIGN] Brian Holt. I don't know if you knew that. I speak Italian, obviously, you don't.

[00:06:22]
So inserting into the views, it's a neat trick, but obviously could see it's not the most useful thing. I just thought it was kind of a funny thing.
>> So it went through the view into the source statements?
>> Yeah, you can see here what querying from category means.

[00:06:39]
Cuz a view doesn't exist, right? It's just a lens to look at a table through. It's a query that gets run, and then you're selecting from the result set. That's actually more accurate than what it is.
>> Is it similar like making a temporary table or?
>> A temporary table actually can't store things.

[00:07:00]
No, it doesn't, cuz I guess it's just purely transitional, right? I guess, depending on what you mean by that, maybe. We're not talking about temporary tables today, but if you're doing, Crazy things and you have to create intermediary tables to then select from, and that's more complicated than result sets, then you can use a temporary table.

[00:07:27]
If you're thinking about it as a transitive, cuz it's not transitive, it's really just what happens is you run your query against the view. The view runs the underlying query, gets all that data, and then you select from the result set as if it was a table. I guess that intermediary there that you could think of kind of as a temporary table, I suppose.

[00:07:51]
>> Yeah, I guess the question came from, I was Googling other ways to speed up queries beyond indexes. And one of them was, use temp tables, and don't do nested views. So I was like, I knew they were different, but I was kinda trying to figure out, yeah,

[00:08:05]
>> Yeah, you can have views on views, right?
>> Yeah.
>> So is it faster to query the view than to query the original table with the WHERE clause for English?
>> It's probably about the same, I would imagine. This is not a performance tool. That's a good thing to keep in mind.

[00:08:27]
This is a convenience and security tool.
>> The quality of life.
>> Yeah. That was 641. What was our original query? CREATE VIEW Actually, the same.
>> It's exactly the same. [LAUGH]
>> Yeah. So it's gonna be similar, but I could see the point of, if you're having views, go through views, go through views, the hops aren't free.

[00:09:11]
They're probably pretty cheap, but they aren't free.