The first and probably most important thing I'm going to tell you about writing good queries or fast queries or performant queries is use the indexes that we spent all that time creating. And I know that this sounds like, well well, duh, the indexes are derived from our access patterns, from the queries. Yes, they should be, but you can still turn around and shoot yourself in the foot if you obfuscate your index. Let's look at what index obfuscation is. The way that you obfuscate an index is you take your index column and you hide it.

You hide it under a bushel so that SQLite cannot see that index. It becomes a black box and it has no idea that inside of there is some sort of indexed value. So if we were to do select star from users where birthday equals in fact, let's find all the people born in 1989. Well, we could do that if we did strftime, and then we did percent y for year on birthday which comes out as a string. So we're gonna juggle that.

And then we do limit 2. So, these people were born in 1989 so it is correct. Like the query executed correctly which is always a good start. You want your queries to execute correctly. However, scan users, not great.

So what has happened here, Even though we have an index on birthday, we do not have an index on, the formatted version of the birthday. And so when SQLite looks at it, it says, yeah, man. I mean I have an index on birthday, but you're asking me for something completely different which I don't have stored in that secondary data structure already, so I'm gonna have to do all the work anyway. I'm just gonna scan the table. So whenever you see something like this and you think, man, that query is is sure a lot slower than I thought, likely it's not using your index because your index is obfuscated.

What can you do to un obfuscate, de obfuscate? What can you do to not hide your index from SQLite? You gotta unwrap it. As much as possible, you have to unwrap it. So in this case, this one is actually pretty easy.

We could do select star from users. Select star from users where birthday between, 198901 goodness. 01 011989, 12/31, and we will limit that to 2 and that gives us those people again. But you see it's finally, thankfully, using that BD index. Let's go ahead and remove the limit and let that totally run and you see it's just counting up and up and up.

And hopefully at 12:31 there is our final person. So if you can, you want to make sure that you're not hiding your indexes from SQLite. So you have to think about this, specifically when you're doing date math like this or if you're doing any other sort of math or, concatenation. You want to move everything you can to the other side of the operand such that you leave your column alone. You don't wanna touch the column as much as possible because you want all the operations to happen on the constant side where we're saying, what the value we're comparing is and you want the index to be used for the column.

So rule number 1 of writing good queries, use your indexes which means do not obfuscate them.