Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
Welcome back to modulate. This is less an 8.4 full table and index scan. Now we have mentioned the full table scan before, but this lesson will be taking a closer look at it. In general, the full table scan as well as the full index skin are not good things, and you typically want to avoid them in your queries.
00:20
Now, just to go over that again, a full table scan is winning. Query has to examine all the records on a database table.
00:28
The common unintentional causes of Missing index. You may have a programmer or you yourself may have written a pretty good query, but there's no good index for that query. In such a case, it might warrant creating an index on that table to solve that problem.
00:42
Now the common intentional cost is a program needs all records for processing. For some reason,
00:50
one example might include a state table that only has 50 records, and we don't really expect that table to grow that much. It's it would be a reference to the United States, and we don't expect the United States to begin adding dozens or hundreds of records that represent different states.
01:06
However, such scenarios are unusual, and sometimes you do want to look into him to make sure that the context of that table makes sense, or the context of that query makes sense.
01:17
Now moving on the full index scan, the performance is actually worse than a full table scan.
01:23
You will want to drop the index to use a full table scan if is needed. For example, if you want to select all your employees and your selecting where employee I D is greater than zero because you know that's a clever way to do it. Well, you'd actually want to remove that reference or that filter
01:42
and just let the full tables can occur.
01:44
And this is because the full index scan has performance that is worse than the full table scan because there is some computational weight related to check in the index. So if the database engine needs to check every record against the index, that's going to take longer than just doing the full table scan.
02:02
So if you do have a reason to do a full table scan,
02:06
you do want to make sure that you are not doing a full index can as the performance is worse. Now. With that, let's go ahead and jump into the development environment and take a look at some examples.
02:19
All right, here we are at the development environment. I have three queries on the script. The pain. The top query is a reference to the system that statements with full table scans view that we discussed in a previous lesson. So if I run that query, I'll see a list of queries that useful table scans.
02:37
So you can use this to check what kind of queries are running against your database. They're using full table scans
02:43
now. If you look through this and you do see the use of the limit question mark a lot, Where that's coming from is actually when you type in how many records you want and D beaver Deep Beaver automatically appends that limit that limit and that amount to your queries that you run. So that's where that's coming from.
03:00
In case you were curious about that.
03:01
Now, moving on, we take a look at this Cleary. We'll take a look at the Data Day returns.
03:07
We see that it's using the company i d. The Tran type and the reference number.
03:12
We go look at the table architecture.
03:15
We'll see that the PM Tran UK one references those columns so it looks like the programmer might have attempted to use the index. But if we use the explain command to look at the execution plan for this query,
03:31
we'll see that it's of type all which means that it's doing a full table skin, and the index is not being used, though at first glance you might believe the index is being used. Now, how can we resolve this? Well,
03:46
the first thing I would do is get rid of this reference number is like a wild card because this is simply saying I want every
03:53
record back. I don't care what the reference number is. Well, if you don't care what it is, you simply get rid of that.
04:00
So now we have the two. The two columns of that composite key or that composite index, So we're actually missing one now. We don't have
04:12
the
04:14
reference number, So if we run, explain again,
04:17
we'll see that we're still doing a full table scan.
04:20
So how do we fix this? Well, this is where we can actually use one of those commands to force the use of an index. We can see that the execution plan is indicating that there are possible keys so we could use the PM Tran underscore UK one key,
04:40
which is that key that we're looking at The execution plan decided not to use it because we didn't have a referenced all the columns there.
04:48
Well, we can force it to use it.
04:50
And again, you want to make sure you get the syntax right here, So this will go above the filters. So we're gonna type in the keyword force
04:59
index,
05:03
um, the brackets, and then we need to copy
05:08
that key name.
05:12
So if I paste it in there, I'm gonna delete the other one because I just want to use this one,
05:15
and I run the explain command again.
05:18
Now, we changed to type reference, which is a lot better, and we're still returning a lot of rose. But if you actually go and look at the data,
05:28
that's actually about the number of rows that we need to return anyway, Based on that query, because a lot of them are of trans I p m.
05:39
Okay, so moving on to take a look at this query, you might initially think that it's a full table scan,
05:46
but if we do and explain
05:50
and we run that will see that it's actually of type Index and were returning. All the records are all the roads, so it is a full index skin.
06:01
So how would we fix this query?
06:03
Well, if that is indeed what we want, which would actually be very dangerous on a transaction table. But if that is indeed what we want, all we have to do is get rid of the order by
06:15
now. If we take a look at it and we run the explain command again, we'll see. That is now a full table skin.
06:23
So with the first query, it was doing a full table scan. But we, however, revised it to correctly use an index.
06:30
And with the second query, we changed them from a full index scan to a full table scan.
06:34
So let's go ahead and head over to the summary before we close out the lesson.
06:40
So that brings us to the 8.4 summary. So what did we discuss in this lesson? Well, we looked at examples off the full table scan as well as an example of a full index can now full table scans are when all records are examined by the database engine, you can resolve them by index targeting.
06:58
We can use a command to force index use if necessary, which is what we did. In the example.
07:03
We used the command force index toe force the database engine to use the index that we indicated.
07:11
Now we also looked at a full index. Can you can switch these two a full tail scan if all the results are actually necessary?
07:18
And if not, you could switch your index use
07:21
just like we did in the full table scan example
07:26
now in general. And this is worth repeating. You want to avoid both the full table and the full index scans whenever possible. It is a common problem found in the field,
07:38
so that completes this lesson, and I hope to see in the next Thank you

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor