Module 8 Challenge Part 3

00:00

I welcome back to modulate. This is less than 8.8 challenge three and this challenge. We're going to examine the provided quarry for optimization problems, so we're definitely continuing. That trend of analyzing queries are analyzing the provided queries for opportunities to optimize them.

00:16

This is definitely a good module, good lessons to pay attention to,

00:21

because if you get good at optimizing queries that you're going to be excellent at just writing queries in general now. That said, let's head over to the requirements document and see what we need to do to solve this challenge. All right, here we are at the requirements document. So let's go ahead and read through this. It does look like it's short, concise and to the point,

00:39

which you know is nice sometimes.

00:41

So the requirements are as follows utilizing the automatic A database and the provided query. Optimize the query.

00:49

Programmer instructions are. Examine the provided quarry. Remove full table scans, implement appropriate index use wind possible add indexes if necessary.

01:00

All right, well, go ahead and pause the video. Take a look at the provided query and see if you can solve the optimization problems with the query on your own before reviewing the solution.

01:11

Now let's go ahead and review the query and see if we can't work through a solution.

01:15

Okay, here we are at the quarry. So the first thing I'm gonna do is just run the query to see what kind of results I get.

01:22

Okay.

01:23

Next, I'm going to go ahead and tag in the explain command just to see what's going on.

01:30

You see that we have what looks like to be three

01:34

full table scans,

01:36

and there's also something else that's pretty bad here. Is it using foul sort? Using temporary is also considered bad, but sometimes it's necessary, especially when you have ah, group by statements. But we'll see what we can do about this. I know we're going to need to get rid of the file, sort,

01:52

because that is considered a bad thing to have. That means they cannot sort.

01:56

You cannot use the index to start the results, so it's ah,

02:00

it's a time consuming process.

02:05

With that said, let's go ahead and move deeper into this query. I'll go ahead and put explain on this query.

02:13

We already know this is bad. We shouldn't be doing a sub query

02:15

Teoh in a filter expression.

02:19

So go ahead and

02:21

run the explain. We'll see that there's a sub query in the select type. You want to avoid those those air time consuming. This one is probably not too bad. But in general, you definitely want Teoh avoid using sub queries in your filters when possible.

02:36

So the easiest way to do that is move that into a joint.

02:40

So because it's in the filtering section, I'm going to go ahead and use an inner join.

02:46

So Jordan Company

02:49

see

02:53

keystrokes right here on C the company I d

03:00

equals

03:01

he peed a company idea,

03:05

All right. And because that is the apparently the source table for companies, there's not gonna be another key to use.

03:14

And we're gonna use it in the filter.

03:17

See that

03:21

cos CD

03:23

equal

03:24

company.

03:29

Go ahead and get rid of this.

03:31

Andi explain

03:35

So we can see that I have

03:38

full table scans in this and I still have full

03:40

our file sort.

03:43

So one thing I can say for sure, and you do see this from time to time and it's definitely a good idea to practice it, because the more you practice, the more you get used to write enquiries and working in sequel and you'll notice things that just simply don't belong. For example, this order by that's not doing anything for us because we're using this derive table

04:01

later on

04:02

in the query. So this order by has no positive impact for us. Let's go ahead and get rid of that run. Explain again.

04:15

Okay,

04:16

so I'm pretty sure this company CD is not an index, which gives us a choice. Can you make it in index, or can you move to a index field?

04:28

I have a feeling that we want to use the name here again. Its preference. There's no,

04:31

um, necessarily answer. That's much better than the other. We could switch to the company I 82 but then we won't get the word. So that's less intuitive. Toe what that means. I'm gonna go ahead and make this company CD and index.

04:46

So property create new Index

04:49

cos CD

04:51

Ongoing Click OK,

04:55

I'm gonna save that. Persist that

04:58

and go back and run the explain on this query again.

05:01

Okay, We still got one more all type

05:05

and that is on DP. And what is DP DP is et department

05:13

using the description.

05:15

I don't believe that's going to be an index.

05:19

So again, that's not an index.

05:23

If we look at the data, we can see the description is finance and the department ideas also finance because they're so similar. I'm just gonna go ahead and switch to the I D.

05:35

Okay, go ahead and run. Explain on this one again.

05:41

Using where? Using where? Index. Okay, so this query, this table expression seems to be as good as it gets. So I'm gonna go ahead and get explain out of they're gonna run the whole explain again, see if anything changes.

05:53

And it already looks like we're making a lot of progress. We only have one more type of all to get rid off.

06:00

So let's see what we can do here.

06:02

No, go ahead and explain this.

06:05

The second table expression.

06:09

See, we got any problems in here.

06:12

Get a type all file sore, temporary. Go ahead and get rid of this order by again. That's not doing anything positive for us.

06:19

Let's go ahead and do it. Explain

06:23

using temporary. We might need that just because we have the group by statements and with table expressions mangling it mangled into the whole query. Could be really tough getting rid of that used temporary in that extra column. But we do have type. All.

06:41

So what can we do about that?

06:43

This from PM Tran. We're focused on the company I d and the B account I d Let's go ahead and see if those are index fields.

06:55

So pm Tran

07:00

and go to properties. We goto indexes.

07:03

So we're looking for a B account. I D and company i d.

07:10

I don't see them.

07:11

So I'm just gonna go ahead and old. I'm going to go ahead and add it.

07:16

Company I d

07:20

be count. I d. Okay, Save persist,

07:25

since I already have that name. So I will just put a three

07:30

at the end of that

07:33

and it's got in.

07:35

Run that explain again.

07:40

Okay, so now we got a type of index, which is better.

07:43

Um,

07:45

someone go ahead and leave that as is gonna run the explain on the full query again.

07:51

So we do have a type of index down here,

07:56

so that's a little bit better than a full table scan,

08:01

but I think we could make it. Ah, make it more efficient.

08:05

So I'm gonna get rid of this. Where be account ideas? No.

08:09

Let's see what explains says after that. Still, the same thing we're gonna do is I'm gonna go ahead and use this,

08:16

um, table expression at the top within the transactions table expression.

08:20

So I'm going to join

08:24

employees

08:26

e

08:28

on e dot company I d

08:33

equals

08:33

pita company I d

08:37

and

08:37

eat at the account. I d

08:41

equals

08:43

p dot

08:46

The account i d

08:48

on. We go ahead and run that again.

08:52

Let's see what we have.

08:56

So we don't have any

09:00

Any full table scans were now using the indexes. All this type columns looks much better. The only issue I see here is using the temporary, but that's coming from the group by statement

09:09

would be really tough to get rid off. So we're gonna leave that as is,