Joining Between Views

[00:00:00]
>> Let's keep talking about views because obviously you can't get enough of them. So let's go ahead and grab this expensive query over here, Which is this one. I don't wanna type it all. So we're just gonna grab it like this. So basically I went and grabbed from people p I grabbed the cast, and then I grabbed all of the roles, and the movies that they were in.

[00:00:40]
So imagine I'm making some sorta page that shows various actors and actresses and the roles that they played and the movies that they were in right you can imagine that being really useful but writing this query over and over particularly if I have to join it again to other things that's just gonna suck and I don't wanna do that.

[00:01:04]
This is another place where a view is gonna be super useful for you. So instead of doing all of this again, I'm just gonna grab the query here again, I'm gonna create a view called actors_ roles_movies. To where it's literally just that same query again, again leaving off the limit five.

[00:01:27]
And now, instead of having to do this big stupid query I can say SELECT * FROM actors_roles movies LIMIT 10 and I just get everything that I had there, right? So much easier to deal with. Hopefully you're starting to see as we build it up, there's like this is where this gets really useful, especially when you have big complicated tables to this.

[00:01:52]
We do lots of joins, you can create these views that make it easier to kinda work with data. The nice thing about views is that they are always current. Right? All it's doing is it's running a the underlying query first, giving you back that result set, and then your query is querying that result set.you cannot index a view.

[00:02:16]
So I can't say create index on, actors roles movies, because if you think about it, that doesn't make any sense. Because you're not queering the actual view the view is just a window to look through. You're actually querying the underlying databases. So if you have a slow view, you need to index whatever the view is query, right?

[00:02:40]
Does that make sense? We will see in a second you can index a different kinda view called a materialized view. But that'll be in the next section. Yeah, this is just way easier to look at. Now the nice thing about this is we can now start joining from this table two other tables.

[00:02:59]
So this actually you can treat views 100% like you treat a table, which is, part of the power here. Imagine if I had to join this, somewhat gnarly query to this somewhat gnarly query. That would be unfun is the term I would choose for that. If you would have a very very long query, instead what we can do here is we have we can select from actors_roles_movies, that view that we made.

[00:03:31]
And then we can use that basically as a normal table and we can do joins based on arm that movie ID which is refers to actor_roles_movies, and we can get this kinda built up query. So this question is asking what types of actors and actresses tend to act in the same sorts of movies?

[00:03:53]
So, the one thing I'm gonna say here, because this is joining from a view, which is already kinda an expensive view, this query is gonna be the most expensive query that we knew and I have run so far. So I'm not gonna write all of this because it's a lot and I don't wanna write all of it.

[00:04:08]
If you're running on a really slow computer, and you still wanna see results, just comment out this or delete these comments, and it'll limit the scope down a little bit, so it'll be a little bit of less expensive to run but now I can run this you can see that is taking a decent amount of time to run and surprise the actor that far above X in the same sorts of movies over and over again, it's Jackie Chan and martial art movies, right?

[00:04:41]
But, who else do we know in here? Robert De Niro, x and a lot of movies about New York. I'm not surprised by that. And Jackie Chan is in movies about Hong Kong a lot, and Stan Lee is in a lot of superhero movies. Okay, that tracks. And Tara Strong, she's the voice actor for Harley Quinn, right?

[00:05:10]
And she acts in a lot of movies about Gotham City, which is where Batman is, right? So that tracks as well. For fun. Let's get this all the way up here to the top. Let's do an explain analyze on this Bad Boy. 295409 It's really interesting that all of this is in a prep.

[00:05:45]
The actual execution of this is 0.05. Where it adds 0.07 execution. All of it's in the set-up. But anyways, suffice to say, this so far is by far the most expensive query that we've run. It took, Time, yeah, it took 3.6 seconds to run obviously this is not something you're gonna wanna run in production 3.6 seconds on a very fast back is gonna translate to crashed servers for sure So I'm gonna show you in the next section how to actually make this a very fast query.

[00:06:29]
We are gonna speed up this query by 4,000,000%. Not bad.