We just finished talking about composite indexes, which is, when one index contains many columns. Right? Now we're gonna talk about covering indexes. Covering indexes are not a discrete thing. There is no way to create a covering index in SQLite or any other database, frankly.

A covering index is a normal index whether that's over 1 column or many columns. It's a normal index in a special situation. So a single index may be covering for 1 query and not for another query. And when we say covering, that means it covers the needs of the entire query. So anything that the query needs can be satisfied out of this index.

Let me show you how. We still have that multi index laying around. And if we did pragma I don't know why I can never remember this. Index info multi. So if we do that, we see that we've got first name, last name, and birthday in that index.

Now, let's clear this out and say select star from users where first name equals Aaron. We know that this is going to use the index because it forms a left prefix. The first name is the first column. So we know that we're going to use the index. It says using index multi.

Let's just narrow this down to just 3 so that we have a little more screen real estate. This is what I want to show you. I wanna show you how this multi index can be a covering index in a certain situation. So let me show you. If we were to do, this is a little bit silly because selecting first name where first name is Aaron.

You might as well have just taken your pencil and written down Aaron, but that doesn't matter. Look, search users using covering index. So we got that word that I was looking for. So let's make some space for the big show. Covering index multi.

So this is the same exact index as before. So let's run, select star again. We'll scroll up just a little bit. So when we selected first name, we used a covering index. When we selected everything, we just used the index.

That's interesting. So if you'll remember, I said a covering index is when an index covers the needs of the entire query. What this means is that SQLite can go to that multi index. It can go to that b tree, do all of the work in that b tree and skip the table and hand the results directly back to the user. So it doesn't have to do that secondary lookup in the table b tree because it was able to get all of the information that it needed straight out of that index.

Let's keep going and see if we can start to gain a little bit better intuition here. So we know that that multi index contains first name, last name, birthday. So if we were to do last name, you see we're still using the covering index. This is as as fast as it can get. You're you're satisfying a query without ever touching the table which is super awesome.

We can go ahead and add, we can add birth day to that list and we still get covering index. If we add even a single other column is pro, it says using index. Because is pro is not contained in the index So it does all the work in that index b tree, takes the row ids, hops back to the table and checks for the is pro over there because it doesn't exist in the index. There is something interesting here. If we drop out if we drop out last name and drop out, Ispro, we still get the covering index.

Now I've just been going on and on and on about left to right no skipping. Right? But here we skipped last name. But we didn't really skip it so we used the index to find all of the people named Aaron. And while once we found all of those people named Aaron, SQLite looks at it and says, oh, that that birthday, that's in the index too.

I'm not gonna do any filtering on it. I'm not gonna narrow down anything. I'm just gonna grab that birthday value while I'm here. And then we'll skip the table on the way home and just hand the results back to the user. So when it comes to using, when it comes to using the column as a part of the select, you you don't have to go in order and you can skip because the equality or the narrowing down must follow the left to right no skipping.

But while it's over there, it can go ahead and grab the data that it needs. Why not? You know what else is over there in that b tree? The row ID. The row ID must exist in that index to know how to phone home to that table to grab the row.

So we can, if we, let's just arrow up and we can say, select ID, first name and birthday. Now we did not put we did not put id in that index but it exists in that index, right? That ID exists in that index. In fact, should we do an imposter table? Should we do it?

Let's do it. Let's do an impostor table. So we need to do, we need to do SQLite 3. I think it's unsafe testing. Yes.

Okay. So we'll turn box mode on, and then what do we do? We do imposter, and then oh goodness. I think it's multi with multi IDX. So I think we give it the name of the index and then the name of the temporary table that we want.

Yes. Do not do this. Do not touch this. Select star from multi idx limit 10. Boom.

Look at that. This is awesome. Impostor tables rule, never use them I guess, but they totally rule. This is the index. So as long as as long as the query only needs first name, last name, birthday, and row ID, which in our case, ID is an alias of row ID because we have an integer primary key.

As long as, the query only needs any of these values, then it can use a covering index which is gonna be super duper fast because it doesn't do a second lookup. It doesn't have to go to the table whatsoever. And so I I think I think that's probably it. This is the best visual representation. I'm glad we have impostor tables.

But you can also use the parts of the index to sort as long as that index part is within the index, you're still in covering index world. So if we were to do select star from can't do star, silly. Aaron, select first name, ID and first name from users where first name equals Aaron, and then we can say order by last name limit. Let's do limit 5. And if we do that, I gotta turn a eexp back on and let's run that query again.

What? I don't Did I do that wrong? EQP is on. Mode is box, and then run it again. There we go.

So, we're still using the covering index even though we're now ordering by the last name because that last name is a part of the index and we've unlocked the ability to use last name because we put a strict equality on first name. Therefore, we are still moving left to right. Covering indexes are awesome. They're kinda hard to come by. You see the rules are very strict cause the moment you add another column that you're selecting out of that table, it's over.

If it's not in the index, it's over. You gotta go phone home to the table to get that other column. So I don't usually design for covering indexes but if you find one that's awesome. Or if you have a super hot path and you need the queries to run as fast as possible and select as just the least amount of data as possible, designing for a covering index can be good because remember that index can be used in other queries. It's just a covering index in a specific query.

Now this, this brings up the the age old, piece of advice that you you should always select only what you need. You should select only the data you need and frankly, that's not as important in SQLite because you don't have any sort of network overhead at all. But selecting only what you need, will possibly help you use a covering index where if you were to do a select star, you're basically host. You're never gonna get a covering index out of that. So I don't necessarily design them.

If you can find them, they're awesome. If you need them, now you know how to make them. And this adds a little bit of weight to the old select only what you need advice.