‫What's going on, guys, my name is Roseanne, and today video, we're going to explain what's the difference
‫between an index scan and an index only scan?
‫How about we jump into it?
‫So I have here a Paul screw's database at the school and I have a beautiful table here with no indexing
‫or anything like that.
‫So I have an ID, a grade, and the name of the student took a bunch of students table, a very simple
‫table.
‫Right.
‫So what I want to select here, for example, if I select the name, where are from grades where I'd
‫equal seven, you can see that this particular person's ID seven is named Hussein.
‫If the select star from grades where it equals seven, you can see that the grade is eighty one and
‫all boring stuff.
‫So what are we to look, to look at as database engineer.
‫We're interested in the, what the database did to fit this role.
‫So we do explain, analyze and we do the same query again, select name from grades where ID is equal
‫seven.
‫And since there are no indexing on the predicate, on ID equal seven, that it was decided to do the
‫only thing it knows where Wedgeworth battle sequence scan, which is the table scan which goes to the
‫table itself and just literally one by one search, every single row pulls it.
‫And the look is it eighty seven.
‫Nope.
‫Let's look at the second one.
‫Is it eighty seven.
‫Nope.
‫Until it finds the ID seven.
‫Right.
‫And it took four hundred milliseconds to do that and it returns one row essentially.
‫That's how we should look here.
‫Right.
‫So that's, that's what it did.
‫So now.
‫We know how to create an index on this idea because it's powerful stuff, so if I do create index ID
‫index on grades and let's say on the ID field.
‫Right.
‫So this way we're going to create a Vetri index so that searching is a little bit faster.
‫And that's a completely different structure from the table.
‫We talked about this table that has, I think, 10 million students.
‫Right.
‫So now we just created another table, given that ID, that beautiful tree soja, that is way better.
‫And searching, we can search that and that index in a very different way than we're searching a huge
‫table.
‫So let's do the same exact thing on this table.
‫And just like that, we went from 300 or 400 millisecond to one millisecond in the next.
‫And we look at this, it says index scan.
‫I want to differentiate between index, an index only scan.
‫We're going to come to that next.
‫It is an index scan because, yeah, we use the index was called ID Index to search for ID seven.
‫And we quickly found out that.
‫However, what's not mentioned here is we had to go back to the table to actually fetch the value name
‫because that's where we're asking for.
‫We're asking for the name of the student.
‫Right.
‫And the name of the student is not in the index, the only value in the index or the IEDs.
‫And we found the ID, but we had to go back to the table.
‫So that table structure we talked about that when we scanned earlier, it's there and we had to go back
‫using the reference, the row reference of the table to jump back to that table, which that item number
‫seven and fix that whole rule that includes the name, the grade and pretty much any other columns as
‫well.
‫And the table itself, beautiful stuff.
‫So if I change this query a little bit, guys, and I asked only for the ID.
‫Does the database need to go to the table, the answer is no, because the idea is right there.
‫Obviously this is a silly query, right, because the value is seven.
‫But look at this.
‫It says no index only scan that is powerful.
‫It says index only scan because it did not have to go to the table and that if you can execute queries
‫in your application so that they are index only scan, you had the jackpot's index.
‫How great are nice, but index only scans that are the beautiful.
‫However, this is a silly query.
‫How do you make this little bit more useful is going to do.
‫Guys, there's something called nunca columns where as we create indexes and I created and I actually
‫made a video about this topic here, what we can drop our index.
‫Let's go ahead and drop our index and we're going to recreate our index.
‫ID idex on grades, on the ID, however, why are we creating that?
‫I know that most of my queries when I query for the ID, are asking for, for example, the grade or
‫for a photo of a name.
‫I barely ask for the grade.
‫I only ask for the name, for example.
‫So I'm going to actually include the name field in this index.
‫This is called Anunnaki column.
‫This is a a key column.
‫This is what we're going to search against.
‫This is what we're going to fetch from the from the index.
‫So it's not it's not going to be using this for for optimization or searching.
‫This is just what we're going to store in the index so we can pull them from a very efficiently.
‫So now if I do this, I'm going to create a brand new index, Semenza index.
‫What what the difference is, we now pulled the values of from the entire table, the name into the
‫index, just the name column.
‫So now if I do the same exact query.
‫And I'm not going to ask for the ID, I'm going to ask for the name.
‫What do you think is going to say that the planner the plan is going to say index only scan.
‫Beautiful.
‫Why?
‫Because we asked the database, hey, find me I.D. number seven, student number seven, but go back
‫and give me the name that it was always like, I'm not going to go back to the table.
‫That huge 10 million square table.
‫I'm staying right there.
‫The name is actually in me in the index.
‫So there you go.
‫Just return it.
‫So that's why I talk much, much faster to execute beautiful, powerful stuff.
‫So now if I change this from name to grade, that's no longer an index only scan.
‫It's an index scan different.
‫Why the grade is not available.
‫And then so we had to go back to the table and fetch the row and we took the hit in order to fetch the
‫grade.
‫So, guys, this is what I wanted to explain in this video, guys.
‫And the tape.
‫Pay attention to the cost of these things, guys, because as we work more with the with a large index
‫and including noncoms, and then this can actually increase the size of the index.
‫And if you increase the size of the index, that could be really bad.
‫Right, because the more the side, the bigger the index that the slower it gets to aquaria, because
‫you had to fetch more and more pages to get into the things that you're looking for.
‫So just be careful that just to have an idea of what what, what, what your query looks like, what
‫your structure looks like, this is very useful thing.
‫That's it for me today.
‫That was the sequential scan index.
‫Can an index only scan of you like this video?
‫Give it a like.
‫Subscribe for more back and enjoy doing stuff I'm going to see in the next one.
‫You guys say goodbye.