1 00:00:02,510 --> 00:00:06,020 For this, I'll create a new collection, 2 00:00:06,020 --> 00:00:10,940 customers here and I'll insert many documents. Now 3 00:00:10,970 --> 00:00:14,210 the first customer has a name, 4 00:00:14,210 --> 00:00:20,270 Max, has an age 29 and a salary of 3000, let's say, 5 00:00:20,490 --> 00:00:35,650 the second document Manu has an age of 30 and a salary of 4000, let's say. I inserted these two documents 6 00:00:35,840 --> 00:00:39,410 and now let's add an index with create index of course, 7 00:00:39,410 --> 00:00:48,310 let's create an index on our name here in ascending order like that. Now if I hit enter, I obviously have that 8 00:00:48,310 --> 00:00:48,790 index 9 00:00:48,820 --> 00:00:59,250 and now I can use my customers and explain with execution stats for more information how it would 10 00:00:59,250 --> 00:01:00,990 actually find Max, 11 00:01:01,020 --> 00:01:04,360 so all customers with a name of Max, only one in our case here 12 00:01:04,410 --> 00:01:05,990 but of course it could be more. 13 00:01:06,480 --> 00:01:11,580 So we see that it of course used an index scan for this because we have an index on the name, 14 00:01:11,580 --> 00:01:14,650 we also got no rejected plans right now here, 15 00:01:14,940 --> 00:01:21,900 we see that one element was returned and it looked at one key in the index and returned one document. 16 00:01:21,960 --> 00:01:26,870 Now I said that this could even be zero docs examined 17 00:01:26,910 --> 00:01:29,920 and the question is when could this be zero 18 00:01:30,060 --> 00:01:35,680 because we need to get the document in the end, the index only has a pointer to the document right. 19 00:01:36,090 --> 00:01:38,460 Well the index does not just have the pointer, 20 00:01:38,700 --> 00:01:42,340 the index has one value and that is the well, indexed value, 21 00:01:42,420 --> 00:01:44,520 so Max in this case and Manu, 22 00:01:44,520 --> 00:01:49,870 so the names are in the index, not just the pointers but also the values for the indexed field of course. 23 00:01:51,260 --> 00:01:57,670 And you can reach a so-called covered query if you actually would find Max 24 00:01:57,740 --> 00:02:07,070 and then also add projection here to not return the ID and only return the name and don't return any other 25 00:02:07,280 --> 00:02:08,360 fields, 26 00:02:08,360 --> 00:02:14,330 so essentially what I do here is I only want to return the fields which are also the indexed fields 27 00:02:14,330 --> 00:02:23,480 or the indexed field in this case, name and now you will see that it actually did not examine any documents, 28 00:02:23,480 --> 00:02:29,120 it returned one but it could do that entirely from inside the index. 29 00:02:29,120 --> 00:02:35,030 Now you will not always be able to reach that state but if you can, if you have some query where you 30 00:02:35,030 --> 00:02:41,210 can optimize your index for that, to reach that covered query state as it is called because the query 31 00:02:41,210 --> 00:02:46,850 is fully covered by the index, then you will of course have a very efficient query because you skipped 32 00:02:46,850 --> 00:02:50,750 that stage of reaching out to the collection getting the documents 33 00:02:50,840 --> 00:02:53,480 and that obviously speeds up your query, 34 00:02:53,630 --> 00:02:58,910 if you can get this to work, you will have a very fast solution. 35 00:02:58,910 --> 00:03:06,020 Now of course it does not make sense to build tens of indexes just to cover all possible queries because 36 00:03:06,020 --> 00:03:08,570 then you'll have a problem with writing again and so on 37 00:03:08,750 --> 00:03:14,390 but if you have an opportunity and you have a query that you typically run and you only typically return 38 00:03:14,420 --> 00:03:19,850 these two fields, it might be worth storing them in a single field or if it's two fields, to store them 39 00:03:19,850 --> 00:03:25,400 in a compound index so that you can fully cover the query from inside your index. 40 00:03:25,400 --> 00:03:30,800 And this is something very interesting to know, that you can reach that covered query state sometimes 41 00:03:30,950 --> 00:03:31,430 at least.