00:00 So I'm going to use the CLI, we'll say SQLite 3, and point it to Prisma DataDB. And then I'll paste in our explain query. And here's that. Explain query plan, select star from note where owner ID equals 1. And our query plan is to scan the note. So a scan's not always a terrible thing.

00:19 Like it's not instantly, oh, it's a scan, it must be bad. But in our particular case, we're going to be getting lots and lots of notes. And so if any time we wanted to find a note for a single user, you can think of scan as like we're looking at every single record, right? So if we're having to look at every single record,

00:36 and when our plans for notes is to have many of them, then that seems inefficient, right? So it would probably be a good idea to add a index to that. So let's go to our Prisma schema here. And we're going to add a couple of indexes to our models here.

00:55 So we'll add an index for the owner ID right here. And the reason we have to do that is because the owner ID is not unique. If we take a look at our migration file here, then we'll see we actually do already have a couple of indexes that Prisma creates for us. And that's for all of the unique fields that we have.

01:14 So the user has a unique on the email and on the username. And then the user image, it's a one-to-one relationship. So we have a at unique on the user ID. But for this one-to-many, where one user can have many notes, we're not going to have at unique on this. That wouldn't make sense.

01:33 So we're going to just tell our database, hey, we need to have an index. Make a special record or special place where you have an index for all of the notes by their owner ID. So we'll add index, and that's going to be owner ID.

01:50 I don't care to specify a name, so thanks anyway, Copilot. So we'll save that. And then let's add one for all of the other models. I just do this as like a default. It's pretty much a pretty solid default for most of the databases to add an index to all foreign keys that are not unique.

02:08 So index here on the note ID. And also always a good idea when you're doing any sort of performance thing at all to add a comment. So we're going to say non-unique foreign key. And we'll do the same thing on this one as well.

02:29 And then we don't need an index here because again, the user ID is unique. We have that index right there anyway. All right, so with that now I can say, let's pull up another terminal, npx prisma migrate dev. And we're going to make a new migration, foreign keys.

02:48 And let's take a look at the migration that was created here. So it created an index for the note owner ID and the note image note ID there as well. And so with that now, if I rerun that explain query plan, and there's our select, then we're going to still get a scan note.

03:07 And that might be because I need to actually perform the migration. Let's see, yeah, our database is already in sync, but it could be that I need to turn this off and start it up again. So let's get our explain query plan, stick that in there and copy that, stick that in there.

03:29 And now we're getting what we wanted. We can search the note using index note owner ID, IDX. If you're curious what indexes you have in a given database, you can actually select that from a special table. So we're going to say select name from the SQLite underscore master,

03:49 where the type is index. And that's going to give us all of the indexes that we've got. We've got auto indexes that are created for us. And then we have the index on those unique fields. And then those indexes we just created ourselves. So at any time, if you want to look at what indexes are available,

04:08 then that is how you accomplish that. So our query is definitely going to be faster. Normally this is something that you want to try and measure somehow. So maybe create a bajillion notes or something and then test that out and like look at querying a specific user's notes. So like we could go to Cody's notes

04:27 and measure how long that query is going to take. And then of course you also have logging going on as well. So then you can take a look at those logs as you're navigating to these different pages and just make sure that the queries are shorter than they were before you made the change. So it's a good idea to make sure you measure

04:44 before you actually implement these indexes. But at the same time, an index and a non-unique foreign key is almost always a good bet. And so I pretty much could say like 99% of the time you should do that. So that is adding a unique or a index

05:03 on a foreign key that is not unique.