Module 8 Challenge Part 2

00:00

Hi. Welcome back to modulate. This is less than 8.7 challenge to and our new bullet points on the slide. Our that we need to examine the provided query four Problems with indexes, and we must also implement improve coding style if possible.

00:17

Now with that, let's go ahead and take a look at the requirements document. They get a more precise look at what we need to accomplish. So here we are. We're at sequel Challenge to Indexes and joins. This is a modulate course challenge, and the sequel program requirements are as follows utilizing the axiomatic a database and the provided query.

00:36

Remove any full table scans.

00:39

Also, remove any bad programming styles,

00:43

and the instructions are as follows.

00:45

Examine the provided query. The query is not using available indexes. Alter the query to use available indexes.

00:53

Do not add any indexes to the database architecture.

00:57

The programmer that wrote the query was also newer and used a sub query when a regular table join would have worked just fine.

01:04

Remove the sub query and use a regular table joint to achieve the same results. And for this solution, you will need the explain command.

01:15

OKay, go ahead and pause the video and see if you can take a look at the provided query and provide a solution before reviewing the answer.

01:25

All right, let's go ahead and head over to the query and see if we can implement a solution for this requirements. Document.

01:32

All right, here we are at the problem. Query. Let's go ahead and work through this and see if we can't reach a solution that satisfies the requirement document.

01:41

So the first thing I'm going to do is just run the query, see what my results look like.

01:46

All right. Next, I'm gonna put an explain committed in front of the query and see what those results look like.

01:53

I could see that we have three layers and one of those layers. See, A has a full table scan involved, because we can see that it's a type all and see a is the cash account.

02:04

So we're gonna go ahead and work through this so that we get rid of that for sure.

02:08

And I also see the sub query here,

02:12

which is probably better implemented as a table join.

02:17

So which problems should we go at first? Well, I'm gonna go ahead and remove the sub query because it's making it a little more confusing to look at in general. And I think implementing a solution to that first will make it look a little bit cleaner.

02:31

So I'm going to do a left join.

02:35

And the reason I'm going to go with the left joint is because the previous programmer used a sub query. It would lead me to believe that he thinks that there's a possibility that there might not be a result

02:47

for the filtering.

02:49

And with the South Korea you'll get back. No. So to facilitate that same approach, I'm going to use a left join. And then if there's no result on that table, I'll also get back no again.

03:00

So I'm less join Sisi Processing Center. Gonna alias that with a c p

03:08

on c p dot company I d

03:12

equals

03:14

c t dot company idea and

03:17

C p dot cash account idea

03:23

equals c t dot cash account I d.

03:27

No. Right.

03:29

So now that we have that, I should be able to erase this

03:34

and just type in CPI dot name

03:39

and then I will, of course, have toe add it to the grouping expression.

03:44

Otherwise I'll get in air

03:47

on, Let's go ahead and just run that. Make sure we're still working.

03:52

And we are.

03:53

So we've removed that sub query and we've moved into just the table joint,

03:58

and then I'm gonna wanna explain command to see if that's changed at all.

04:02

Okay, it looks like we might have made the situation a tab bit worse because now we have two full table scans. Well, let's go ahead and continue resolving the issues and see if we can make that better.

04:15

So I'm pretty confident that this is a bad thing where we're using the description as a filter.

04:21

So let's bring up that table cash account

04:26

when I go ahead and open that up

04:32

and we can see that the Description

04:35

credit card account

04:39

is actually related to the i D

04:43

9 78

04:45

So let's go ahead and implement that instead.

04:48

So we're gonna do

04:50

see a

04:53

dot cash account. I D.

04:56

Is equal to 9 78

04:59

That's using a index now, I believe

05:02

so. We can double check that by looking at the properties going to the indexes.

05:08

We could see that the primary is the cash account i d and the company, I d

05:12

Okay, now

05:15

we could have potentially made the description

05:18

a index, and sometimes you will see programmers do that because then in the code,

05:26

it's much easier to tell what the name is than what the number is. If you come back and you look at this query after you've worked on it and it's some time has passed, you're not likely going to remember what 978 means. But if you've indexed the description of the title, you will be able to read it and immediately

05:45

probably understand what it means.

05:46

Well, with that said, Let's go in and it's run the explain command again and see if we fixed any problems.

05:51

Okay, so this is much better. We have a const, which is the best when you can get in the the The other two are references, which is good. It's definitely not not a table. Full table scan. So we've definitely improved this query

06:06

and we've we've optimized the code by removing a unnecessary sub query and using a join.