Alright. I know that I'm a little bit biased but I think every video in this course is pretty good. Otherwise, I wouldn't have shipped it if I didn't think it was good. However, I'm kinda partial to indexing videos. I love indexes.

I love teaching them. I love learning about them. Indexes are the best way to unlock performance, not only in SQLite, but in in any database. And the good part about this indexing section is much of this does apply to other databases and so the stuff you learn here is gonna serve you well for a very long time. There's a good chance you'll become the database expert on your team if you can fully understand just indexing.

Bee trees. It's gonna get a little computer sciency. I don't have a computer science degree. I got my degree in accounting. So don't worry.

Don't let that scare you off. We're gonna have some good graphics to help understand and lay out these b trees, but they are pretty important. We're gonna talk about where to put indexes, partial indexes, covering indexes, all kinds of good stuff. In this video, I'm gonna give you a little bit of like a theoretical overview of indexing and then we'll dive into specific details in the next videos. So the first thing we need to know about an index is it is a completely separate data structure.

So it's not, it's not like an attribute of your table. It is a fully discrete separate data structure. That data structure is called a b tree or potentially a b plus tree depending on a few implementation details. Your table itself is in fact a b tree. So your table itself is an index.

Everything's an index. Indexes are indexes. Tables are indexes. Everything's an index. So when you create an index, it creates this secondary data structure.

The second thing we need to know about indexes is that they keep a copy of part of your data. So anytime you create an index, it copies part of the data out of the table into that secondary data structure that must be maintained. So anytime you insert, update, or delete in the main table, some work, some maintenance work has to happen in that secondary data structure that we call an index. So when people say, well, you really don't wanna be adding indexes that you don't need. I don't know why I gave the people like a negative tone.

They're actually right. You don't wanna be adding indexes that has to be propagated over into that index. And if there are dozens of indexes, oh, that can that can start to add up a little bit. The third thing that we need to know is that every index contains a pointer back to the row in the main table. Did you get that?

Every index contains a pointer back to the row in the main table. So whenever you create an index, whatever columns you index, those columns are copied out and put into the index along with usually the row ID. Remember row IDs? So usually the row ID goes along for the ride. So it contains a pointer back to the row and I will prove that to you.

Hopefully you believe me, but I will prove that to you here in a little bit. So so far we've got separate data structure, maintains a copy of part of your data and in that copy, there is a pointer back to the row in the main table. Please don't worry if all of that doesn't make total sense right now. This is our first pass at the content and we need some sort of introduction. Right?

So I'm hopefully we're setting up a web of information in your mind and we're gonna catch all of the details in the next videos. So just sit with that for a second. The last thing I will leave you with is when you're building out your schema, it's kinda straightforward. Right? If you have a text piece of text data, you put it in a text column.

If you have integer data, you put it in an integer column. Building a schema is more science than art. Creating good indexes is more art than science. So when you're creating indexes, we're gonna look at a bunch of different places you can put them. But if the data, the data that you have drives the schema, the data doesn't drive the indexes, the queries drive the indexes.

So where you need to put indexes is going to depend on how you access the data. And as presumably an application developer or someone who's very close to the access patterns, you are in the best position to know where to put the indexes. That's why this information is so important for you. So data drives the schema, queries drive the indexes or said another way, Access patterns drive index placement. I think that's it.

I think that's it for the overview video. This is gonna be a lot of fun. This is one of, if not my favorite part about databases. So let's get to it.