We talked about this just for a second in the indexing module but I wanna go over ordering again, sorting your results and importantly using an index to assist that which is always going to be faster than pulling the rows out and then having SQLite sort them after the fact. We would love to just use an index to actually read them in order. So we can do something like pragma index info. Let's do a pragma index list from the users table. That gives us these indexes.

Not super helpful. Then we'd have to go into index info. Since I'm in a GUI, I'm just gonna use the GUI. So we can come over here to structure and see we have name, email and b day which is what we got. B day name and email but this shows us the column names which are pretty important.

So in the name index, we have first comma last. So that's a compound index where ordering matters. Then we have email and birthday as single columns. With that in mind, let's do some stuff. So select star from users limit 10.

It's a good start. Let's throw an EQP on here. Explain query plan. K, good enough. Scanning the whole table.

Yes, we expect that. Order by, where do you wanna start? Let's start with one that is indexed. Order by first name, Scan using index name. Cool.

We expected that. If we throw created at on there, you see use temp b tree for order by. So because we don't have an index on createdat, what SQLite has to do is scan the entire table and then put those things in order. One optimization that SQLite makes is it says, that sure is gonna be slow. I'm going to construct a temporary b tree to accomplish that ordering.

That's what that temp b tree for orderby means. So if we keep going and we go back to first name, you see we are using that index name and then we can also order by last name as well and we're still using that index name because we accessed the first part, the leftmost part which is first name, and then the second part which is last name. We didn't skip anything. If we were to skip something, we're back to the temp b tree. Because this index is created with first name comma last name, we cannot simply skip over last name.

Now, I do wanna show you something interesting here. So if we do first name, yes, scan using index name. But then what if we were to add another thing on here? Birthday's indexed which might confuse you. So let's say create it at which is not indexed at all.

So if we were to add that on there, we see, hang on, use temp b tree but for right part of order by. That's kinda interesting. So what happens here is SQLite looks at it and says, shoot. I can use first name. First name is indexed.

So I can get those things out in order. I can get those rows out in order, but then after that, I can't forward create a dat because created at is not indexed and it's definitely not a part of this name index. So what sequelite does is it does sort of a, it does sort of a block sort. So it says great, I got all these first names. Now let's order those by created at.

Then we'll go to the next first name. I got all those first names. Now, let's order that little set by created at. And so this is still index assisted even if it is not fully index powered. You still get the first part.

The first name is ordered by index and then that temp b tree is used for the second part. Now, again, you can have first name, last name and if you were to drop this created at, you'll see it's using that covering index. Covering meaning this index covers the needs of the entire query. So you're in a good spot there. And of course, when you select like a wide open select like that, it is going to come back.

It is going to come back in row ID order and so if we were to do explain query plan on that, order by row ID, you'll see it just doesn't change at all because that is the default ordering. Row ID, ID, o ID, all of those are the default ordering. That is how the, that is how the data is arranged. Finally, the last thing you can do, first name, last name. So we see using index name and remember if we throw created at on there, you get this temp b tree thing.

Do you remember when we were doing do you remember just recently when we were doing the pagination, video with the row value syntax? I threw the ID on at the end because there are people that share a first and last name. There's more than one Aaron Francis in the world and he got to the Twitter handle first, so I'm Aaron d Francis. There are more than 1, there's more than 1 person named the same thing. So throwing ID on the end disambiguates the sorting.

It makes it deterministic, which is always very important. We want the rows to come back in the same order every time. And if you don't throw this on there, it is up to SQLite to determine which order 2 rows should go in. Should they share a first and last name? We don't want that.

We want to throw ID on there. So when you have created, it uses that temp b tree but when you have ID, it doesn't use that temp b tree because remember this index while it was declared first name, last name, in all reality, the data that is stored in the index is first name, last name, ID because it contains that row pointer to get back to the main table to pull the row out of the main table. So you're always safe safe to append, the ID as a sort of disambiguation at the end because it will always be there and it will not cause a secondary, temp b tree to be created.