1 00:00:00,120 --> 00:00:04,950 ‫What's going on, guys, my name is Roseanne, and today video, we're going to explain what's the difference 2 00:00:04,950 --> 00:00:09,830 ‫between an index scan and an index only scan? 3 00:00:10,440 --> 00:00:11,940 ‫How about we jump into it? 4 00:00:11,950 --> 00:00:20,400 ‫So I have here a Paul screw's database at the school and I have a beautiful table here with no indexing 5 00:00:20,400 --> 00:00:21,510 ‫or anything like that. 6 00:00:22,260 --> 00:00:28,590 ‫So I have an ID, a grade, and the name of the student took a bunch of students table, a very simple 7 00:00:28,590 --> 00:00:28,920 ‫table. 8 00:00:29,400 --> 00:00:29,670 ‫Right. 9 00:00:30,120 --> 00:00:36,940 ‫So what I want to select here, for example, if I select the name, where are from grades where I'd 10 00:00:36,940 --> 00:00:43,020 ‫equal seven, you can see that this particular person's ID seven is named Hussein. 11 00:00:43,290 --> 00:00:50,280 ‫If the select star from grades where it equals seven, you can see that the grade is eighty one and 12 00:00:50,280 --> 00:00:51,540 ‫all boring stuff. 13 00:00:52,140 --> 00:00:55,310 ‫So what are we to look, to look at as database engineer. 14 00:00:55,350 --> 00:00:59,570 ‫We're interested in the, what the database did to fit this role. 15 00:00:59,580 --> 00:01:07,980 ‫So we do explain, analyze and we do the same query again, select name from grades where ID is equal 16 00:01:07,980 --> 00:01:08,320 ‫seven. 17 00:01:08,730 --> 00:01:16,290 ‫And since there are no indexing on the predicate, on ID equal seven, that it was decided to do the 18 00:01:16,290 --> 00:01:22,260 ‫only thing it knows where Wedgeworth battle sequence scan, which is the table scan which goes to the 19 00:01:22,260 --> 00:01:28,650 ‫table itself and just literally one by one search, every single row pulls it. 20 00:01:28,740 --> 00:01:30,850 ‫And the look is it eighty seven. 21 00:01:30,870 --> 00:01:31,380 ‫Nope. 22 00:01:31,380 --> 00:01:32,250 ‫Let's look at the second one. 23 00:01:32,250 --> 00:01:33,010 ‫Is it eighty seven. 24 00:01:33,060 --> 00:01:33,410 ‫Nope. 25 00:01:33,600 --> 00:01:35,400 ‫Until it finds the ID seven. 26 00:01:35,520 --> 00:01:35,820 ‫Right. 27 00:01:36,780 --> 00:01:42,060 ‫And it took four hundred milliseconds to do that and it returns one row essentially. 28 00:01:42,480 --> 00:01:44,160 ‫That's how we should look here. 29 00:01:44,220 --> 00:01:44,460 ‫Right. 30 00:01:44,940 --> 00:01:46,250 ‫So that's, that's what it did. 31 00:01:46,290 --> 00:01:46,950 ‫So now. 32 00:01:48,320 --> 00:01:55,130 ‫We know how to create an index on this idea because it's powerful stuff, so if I do create index ID 33 00:01:55,280 --> 00:01:59,180 ‫index on grades and let's say on the ID field. 34 00:01:59,860 --> 00:02:00,200 ‫Right. 35 00:02:00,440 --> 00:02:05,210 ‫So this way we're going to create a Vetri index so that searching is a little bit faster. 36 00:02:05,450 --> 00:02:08,210 ‫And that's a completely different structure from the table. 37 00:02:08,210 --> 00:02:11,900 ‫We talked about this table that has, I think, 10 million students. 38 00:02:11,900 --> 00:02:12,150 ‫Right. 39 00:02:12,500 --> 00:02:21,500 ‫So now we just created another table, given that ID, that beautiful tree soja, that is way better. 40 00:02:21,500 --> 00:02:29,900 ‫And searching, we can search that and that index in a very different way than we're searching a huge 41 00:02:29,900 --> 00:02:30,250 ‫table. 42 00:02:30,620 --> 00:02:33,460 ‫So let's do the same exact thing on this table. 43 00:02:34,130 --> 00:02:40,220 ‫And just like that, we went from 300 or 400 millisecond to one millisecond in the next. 44 00:02:40,520 --> 00:02:43,150 ‫And we look at this, it says index scan. 45 00:02:43,250 --> 00:02:46,430 ‫I want to differentiate between index, an index only scan. 46 00:02:46,430 --> 00:02:47,620 ‫We're going to come to that next. 47 00:02:48,380 --> 00:02:56,530 ‫It is an index scan because, yeah, we use the index was called ID Index to search for ID seven. 48 00:02:56,600 --> 00:02:59,420 ‫And we quickly found out that. 49 00:03:00,170 --> 00:03:09,860 ‫However, what's not mentioned here is we had to go back to the table to actually fetch the value name 50 00:03:09,860 --> 00:03:11,270 ‫because that's where we're asking for. 51 00:03:11,270 --> 00:03:13,550 ‫We're asking for the name of the student. 52 00:03:14,420 --> 00:03:14,900 ‫Right. 53 00:03:15,050 --> 00:03:19,970 ‫And the name of the student is not in the index, the only value in the index or the IEDs. 54 00:03:20,180 --> 00:03:23,660 ‫And we found the ID, but we had to go back to the table. 55 00:03:23,840 --> 00:03:29,900 ‫So that table structure we talked about that when we scanned earlier, it's there and we had to go back 56 00:03:29,900 --> 00:03:37,430 ‫using the reference, the row reference of the table to jump back to that table, which that item number 57 00:03:37,430 --> 00:03:44,150 ‫seven and fix that whole rule that includes the name, the grade and pretty much any other columns as 58 00:03:44,150 --> 00:03:44,440 ‫well. 59 00:03:45,020 --> 00:03:47,540 ‫And the table itself, beautiful stuff. 60 00:03:48,710 --> 00:03:54,140 ‫So if I change this query a little bit, guys, and I asked only for the ID. 61 00:03:55,470 --> 00:04:01,640 ‫Does the database need to go to the table, the answer is no, because the idea is right there. 62 00:04:02,280 --> 00:04:05,600 ‫Obviously this is a silly query, right, because the value is seven. 63 00:04:05,910 --> 00:04:06,740 ‫But look at this. 64 00:04:06,750 --> 00:04:12,500 ‫It says no index only scan that is powerful. 65 00:04:13,020 --> 00:04:21,270 ‫It says index only scan because it did not have to go to the table and that if you can execute queries 66 00:04:21,270 --> 00:04:26,430 ‫in your application so that they are index only scan, you had the jackpot's index. 67 00:04:26,430 --> 00:04:31,040 ‫How great are nice, but index only scans that are the beautiful. 68 00:04:31,110 --> 00:04:33,110 ‫However, this is a silly query. 69 00:04:33,420 --> 00:04:37,150 ‫How do you make this little bit more useful is going to do. 70 00:04:37,190 --> 00:04:45,840 ‫Guys, there's something called nunca columns where as we create indexes and I created and I actually 71 00:04:45,840 --> 00:04:50,190 ‫made a video about this topic here, what we can drop our index. 72 00:04:50,190 --> 00:04:54,810 ‫Let's go ahead and drop our index and we're going to recreate our index. 73 00:04:55,940 --> 00:05:01,890 ‫ID idex on grades, on the ID, however, why are we creating that? 74 00:05:02,240 --> 00:05:11,540 ‫I know that most of my queries when I query for the ID, are asking for, for example, the grade or 75 00:05:11,540 --> 00:05:13,220 ‫for a photo of a name. 76 00:05:13,220 --> 00:05:14,630 ‫I barely ask for the grade. 77 00:05:14,630 --> 00:05:16,430 ‫I only ask for the name, for example. 78 00:05:16,580 --> 00:05:22,150 ‫So I'm going to actually include the name field in this index. 79 00:05:22,430 --> 00:05:24,400 ‫This is called Anunnaki column. 80 00:05:24,650 --> 00:05:26,030 ‫This is a a key column. 81 00:05:26,040 --> 00:05:27,890 ‫This is what we're going to search against. 82 00:05:28,100 --> 00:05:32,120 ‫This is what we're going to fetch from the from the index. 83 00:05:32,510 --> 00:05:37,070 ‫So it's not it's not going to be using this for for optimization or searching. 84 00:05:37,070 --> 00:05:43,130 ‫This is just what we're going to store in the index so we can pull them from a very efficiently. 85 00:05:43,340 --> 00:05:48,890 ‫So now if I do this, I'm going to create a brand new index, Semenza index. 86 00:05:48,920 --> 00:05:56,540 ‫What what the difference is, we now pulled the values of from the entire table, the name into the 87 00:05:56,540 --> 00:05:58,010 ‫index, just the name column. 88 00:05:58,370 --> 00:06:00,620 ‫So now if I do the same exact query. 89 00:06:01,570 --> 00:06:05,450 ‫And I'm not going to ask for the ID, I'm going to ask for the name. 90 00:06:05,770 --> 00:06:12,010 ‫What do you think is going to say that the planner the plan is going to say index only scan. 91 00:06:12,460 --> 00:06:13,170 ‫Beautiful. 92 00:06:13,180 --> 00:06:13,630 ‫Why? 93 00:06:13,870 --> 00:06:21,070 ‫Because we asked the database, hey, find me I.D. number seven, student number seven, but go back 94 00:06:22,000 --> 00:06:27,310 ‫and give me the name that it was always like, I'm not going to go back to the table. 95 00:06:27,940 --> 00:06:30,220 ‫That huge 10 million square table. 96 00:06:30,640 --> 00:06:31,920 ‫I'm staying right there. 97 00:06:31,930 --> 00:06:35,500 ‫The name is actually in me in the index. 98 00:06:35,780 --> 00:06:37,240 ‫So there you go. 99 00:06:37,750 --> 00:06:39,250 ‫Just return it. 100 00:06:39,670 --> 00:06:45,220 ‫So that's why I talk much, much faster to execute beautiful, powerful stuff. 101 00:06:45,250 --> 00:06:53,730 ‫So now if I change this from name to grade, that's no longer an index only scan. 102 00:06:53,740 --> 00:06:57,160 ‫It's an index scan different. 103 00:06:57,160 --> 00:06:59,380 ‫Why the grade is not available. 104 00:06:59,380 --> 00:07:06,130 ‫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 105 00:07:06,130 --> 00:07:06,480 ‫grade. 106 00:07:07,240 --> 00:07:10,690 ‫So, guys, this is what I wanted to explain in this video, guys. 107 00:07:10,930 --> 00:07:13,340 ‫And the tape. 108 00:07:13,480 --> 00:07:22,360 ‫Pay attention to the cost of these things, guys, because as we work more with the with a large index 109 00:07:22,360 --> 00:07:29,350 ‫and including noncoms, and then this can actually increase the size of the index. 110 00:07:29,360 --> 00:07:33,460 ‫And if you increase the size of the index, that could be really bad. 111 00:07:33,700 --> 00:07:40,000 ‫Right, because the more the side, the bigger the index that the slower it gets to aquaria, because 112 00:07:40,030 --> 00:07:44,420 ‫you had to fetch more and more pages to get into the things that you're looking for. 113 00:07:44,620 --> 00:07:50,980 ‫So just be careful that just to have an idea of what what, what, what your query looks like, what 114 00:07:50,980 --> 00:07:54,460 ‫your structure looks like, this is very useful thing. 115 00:07:54,940 --> 00:07:55,950 ‫That's it for me today. 116 00:07:56,230 --> 00:07:58,720 ‫That was the sequential scan index. 117 00:07:58,750 --> 00:08:01,960 ‫Can an index only scan of you like this video? 118 00:08:02,110 --> 00:08:03,040 ‫Give it a like. 119 00:08:03,040 --> 00:08:07,030 ‫Subscribe for more back and enjoy doing stuff I'm going to see in the next one. 120 00:08:07,040 --> 00:08:08,400 ‫You guys say goodbye.