Optimizing User Search Query Performance with Indexing
00:00 I'm on my user search page, and if you notice when I refresh, this is taking longer than you might expect. That's because I filled my database with a ton of users. We can take a look at our logs here. We'll see that these queries are taking 900 milliseconds to run this query. What is happening? This is so bad.
00:18 What I did was we're projecting ourselves out into the future a little bit. You can feel free to do this as well if you like. Just keep in mind, it takes a little while to seed the database with this much data. But right here, we've got 15,000 users that we're going to be inserting into the database.
00:35 Again, this is not as part of the playground that has been set up. So you have to update this yourself if that's what you want to do, to update the total users. Then we're also updating the number of notes they can have to be anywhere from 200-300 notes.
00:51 Then I commented out the images section because it's not totally necessary for these notes to have images. In fact, you could probably also do the same with the user image. That might save a little bit of time too. But yeah, it's going to be a fun one.
01:09 Creating all these users and all their notes and everything took me six and a half minutes. So just bear that in mind. The query that we're going to try and optimize is the query we wrote earlier for doing this user search page. There's something about this query that's making it take a whole second,
01:27 and this is going to continue getting worse and worse unless we do something about it. You'll be surprised to find that the solution to this problem is one index. We just need one index on two columns, and that makes this query super fast. That is why when somebody shows up here and they're like, oh, okay, yeah, this is slow.
01:46 Let's add a cache and then move on with our lives. Yeah, okay, you can do that, but now you've got a cache to manage, and all of the complexities with that, I need to make an entire workshop on caching. That is a non-trivial problem. Or you can spend a little bit of time doing what we're going to do in this exercise and really understand what the query is doing,
02:06 and add a single index, and poof, everything is fast and you don't have to worry about managing a cache. So yeah, I think this one's going to be fun. It's going to go pretty deep on explaining these different query plans that SQLite is going to take based on this particular query. You should have a fun time with it for sure.
02:24 Go ahead and take a whack at it, and we'll see you when you're done.