Laravel Performance
This episode is for members only

Sign up to access "Laravel Performance" right now.

Get started
Already a member? Sign in to continue
Playing
09. Adding database indexes

Episodes

0%
Your progress
  • Total: 1h 9m
  • Played: 0m
  • Remaining: 1h 9m
Join or sign in to track your progress

Transcript

00:00
We're going to go ahead and add a new feature to our blog now. And that is the ability to show posts from a particular user.
00:07
Now, I've gone ahead and just regenerated the data that we have in here again, just so we can simulate this with a higher amount of data so we can see the speed impact this has. Now we have five users and each of these users has 2000 posts. Now, if we head over to our home controller, we're paginating this by 100.
00:28
We can even bump that up a little bit more as well. And if we just take a look at this, we've got quite a high query count here anyway, or quite a high speed for this particular query anyway. But that is, of course,
00:41
because we're building a huge amount of data in which you probably won't be doing in reality. OK, so we're going to add this feature in now where we can tack onto the end of a query string, something like user ID equals one. What that's going to do is it's only going to pull back the posts for that
00:55
particular user, probably something that you would likely do. And if you aren't doing it in the query string and you're doing it elsewhere, we're going to have the same effect here as well, because we're going to be adding an index to the posts table.
01:08
OK, so let's build this feature, take a look at the speed and then we'll figure this out. So what we're going to do here is say when and we're going to take in the request here and say when we have a user ID, this is just a conditional feature
01:23
of building up queries where you can take an easy shortcut instead of defining out an if statement. So when the request has a user ID, you could also say has user ID. We want to go ahead and run this callback
01:37
with a query builder to continue building up this query. So there's a couple of ways that we could do this. We could say query where belongs to like this. And we could pass in a user that we find from the database.
01:50
So we could say find or fail and then pass in from that request the user ID and just bring in the request into scope here. That's one way that we could do this. Or we could just do a direct where user ID equals.
02:06
Let's look at this first of all, because that introduces another query, which may not be great. We'll just give this a refresh. OK, so what we can see here is all of the posts from user one.
02:17
And I'm actually going to bump the pagination up as high as I can kind of get this without this running too slowly, and I think that's enough for now. OK, so what's happening here? Well, first of all, we've introduced an additional query here to select that user
02:30
from that user's table, which in itself is quite high in speed. So 20 milliseconds, which isn't great. And we also have in here where posts user ID is one or in one in this case. So what we've done is introduced
02:48
an additional query because we've looked the user up. So in this case, let's just go for saying query where user ID is. The user ID, I think that's perfectly acceptable to do. OK, so let's head over and give this a refresh.
03:04
And you can see here we've still got this count here on the post quite high. And we've also got this here, which is about 10 milliseconds for every single request. Now, what we can actually do with this
03:14
is add an index to the user ID column on the posts table because we're using this with an aware query and we need this looked up in our database. We want to make sure this can be looked up as quickly as possible at the database level when we query this.
03:29
So we increase speed. So how do we add an index then? Well, let's just do this directly within the database, first of all, just to kind of play around with it.
03:38
With indexes, you'll probably find you'll have to just tweak them, remove them, sometimes adding more indexes than you need can actually cause a decrease in performance. So let's go ahead and create an index here.
03:51
And this is going to vary based on the software that you're using. So I'm going to call this post user ID index. The type of this is just going to be a standard index. And we're going to choose the user ID column.
04:02
Let's save these out. OK, so if we head over here, we've got 24 ish milliseconds for the count here. And for this one, we've got about nine or ten milliseconds each time.
04:13
Now, if I just give this a refresh, you can see that this one has definitely reduced down. Bear in mind that we're not working with huge amounts of data here. We're looking at something that displays
04:24
on the page rather than happens on the back end. If, for example, you had a background task which was performing a query like this, you want that to be as quick as possible as well.
04:34
So you can see that that has reduced and we can even bump this up even further. Let's try a thousand just so you can get a good idea of this. So this is hovering about seven or eight milliseconds for this query.
04:44
And if we head over here and get rid of the index and we come back over, so remember, that's about seven or eight. This is bumped up to about nine or ten, twelve and so on and so forth. We have some caching here as well, which improves this.
04:58
But generally, this is going to work to speed things up. OK, so we're actually going to add this index now at the migration level. So how do we do that? So we can either add this onto the create posts table migration.
05:11
So at the point where we have this in here, we could actually add an index in to here and it does depend on the order you do this, so it would probably be at this point that you would add the index, just depends, and that would add the index for you.
05:26
If you want to go ahead and retrospectively add an index, that's just as easy to do. So we're going to go ahead and say make migration, add user ID index to posts table, and let's go ahead and generate that out.
05:40
Add user ID index to post table and we can come down here to the post table. We can say table index and just set user ID. And that's pretty much all we need to do. So if we head over to the database here, there's no index for that at the moment.
05:55
But as soon as we go ahead and migrate this change, we should see that index added inside of here. Great. So if we head over now, we've got a much faster query time here.
06:07
Again, the amount of data we're working with doesn't really show how much faster this is, but if you're working with, say, hundreds of thousands of records and you are querying this in the background somewhere, you're going to see a massive decrease of the amount of time it takes to run these queries.

Episode summary

In this episode, we focus on improving our blog's performance by adding database indexes, specifically to help with showing posts from a particular user. We kick things off by bumping up our test data so we can really see the impact of our changes—each of our five users now has 2,000 posts!

We then walk through adding a feature that lets us filter posts by a user's ID using a query string parameter (like ?user_id=1). We discuss different approaches to filtering, including using query builder methods and thinking about how they affect query speed and database calls.

After we've implemented filtering, we analyze the speed of our queries. Initially, things are a bit slow because the table doesn't have an index on the user_id column. So, we head straight into adding an index manually in the database, experiment with it, and watch our query times drop!

We also talk about best practices, highlighting that you don't want to add too many indexes (as that can actually hurt performance), and demonstrate how to add an index through framework migrations, both when creating a new table and by adding an index to an existing table.

By the end, you'll see just how much indexes can help with speed, especially as your data grows. We wrap up by running a few benchmarks, removing/re-adding indexes, and watching those query times go up and down. It's a super practical walkthrough if you're curious how indexes affect real application performance!

Episode discussion

No comments, yet. Be the first!