Common Performance Issues in Laravel

On 15th of November I finally launched Laracheck. If you're working in a team and you're about code quality, I think you're going to love it. Laracheck is a code review tool that can do more "high-level" or let's say, contextual analysis on your code. For example, detecting N+1 queries or missing database indices in migrations and similar things.

In this article, I'd like to talk about these two things in particular: N+1 queries, and database indices. After a certain time, almost every project experiences some kind of performance issues and these are often the primary source of it.

N+1 Queries

No matter what project I worked on in the past, N+1 queries always caused real performance issues. They usually occur only in production environment and hard to trigger them while developing. But first, let's discuss what is exactly an N+1 query problem. Consider the following snippet:

If there are 100 posts in your database, this, first innocent-looking foreach will trigger 101 select queries. Since the comments relationship is not eager-loaded, Laravel will run an additional query for each post to get the comments associated with it. So these are the queries:

select * from posts

select * from comments where post_id = ?   

The first one runs only once, but the second one runs 100 times. So we have 101 database queries. Not good. Fortunately, Laravel is a great framework and the fix is pretty easy. Instead of Post:all() you need to call Post::with('comments')->get(). It'll eager-load comments to the posts. It'll run only two database queries. One for the posts and one for the comments. You just saved 99 queries with one extra function call!

The problem is, in my experience, it's pretty hard to spot these kinds of mistakes in a code review:

foreach (Post::all() as $post) {}

vs

foreach (Post::with('comments')->get() as $post) {}

The difference in code is only a few words, but the difference in performance is huge.

Usually, the main source of N+1 problems is lazy-loaded relationships. But if you're inside a foreach you don't even need Eloquent relationships to cause N+1 problems. Here are a few other issues:

• Calling DB functions in loops such as DB::table('posts')
• Querying Eloquent model in a loop such as Post::where('published', true)->get()
• Saving, updating or deleting models in a loop such as $post->save()

Each of these requires a loop, for example:

Unfortunately these kinds of N+1 problems depends more on the concrete use-case but usually they require a bit more effort than a simple with('comments'). Here are some general ideas:

• find() and findOrFail() accepts an array as well. So we can fix the foreach above: $posts = Post::find($ids);
• When you're updating models in a loop, usually you can run a single batch update using ->whereIn('id', $ids)
• Sometimes you need to use a "hashmap." So instead of looping through your models and run additional queries you can do the whole thing before the loop and create a big associative array then process it.

You need to be extremely careful when you're dealing with large amount of data. One of constant source of bad performance at my current team is legacy Excel exports. We use the amazing laravel-excel library which provides a map function that accepts a model and you can map the data to your needs:

This is a pretty useful feature. However, the catch is that map will run for every invoice in your application! If you have 10 000 invoices it'll run 10 000 times when the user clicks the export button. So if you write only one query in the function, it'll run ten thousand database queries. Yep. If you write five, it'll run 50 000 queries. And you don't even need tens of thousands of records. Let's say you have only 700 invoices, but in the export you need additional data so you write six queries. The result is 4200 database query. Ouch.

Another dangerous and hard-to-see form of N+1 query is notifications. For example, this is the "free trial about to expire" notification of Laracheck:

Each time this notification is sent out it'll call the allCheckResults() method which will run a few database queries. Let's say three. And once again, numbers add up quickly. If you have 5000 users it's 15 000 queries.

So these are some common occurrences of N+1 problems:

• Lazy-loaded relationships.
• Database queries in loops or Collection methods. Nested loops are even more dangerous.
• Database queries in "indirect loops." By indirect loop, I mean something like toMail in Notifications or map in exports.

You need to be absolutely careful about these situations because they cause real production issues. Fortunately, Laracheck can detect N+1 queries so you don't need to worry about them.

Missing whenLoaded() in Resources

Laravel HTTP Resources are great! However, they can be a source of poor performance. Consider this class:

Nice and simple. Now let's take a look at how we use it in a Controller that returns a list of posts:

Nothing crazy so far. However, this API endpoint (or web route) will perform N+1 number of database queries. First, it queries the posts (Post::all()) then it'll create a Resource for each one. In the PostResource class we perform a select * from comments where post_id = ? query. Since this will fun for every post it introduces an N+1 problem in your application.

We can solve this problem in two steps:

• Only access the comments relationship in the Resource if it's already loaded in the model
• Eager-loading the comments relationship in the controller using Post::with('comments')->get()

This is what the new resource looks like:

The only difference is the whenLoaded() call but it makes a big difference! If the comments relationship in the Post model hasn't been already loaded it will not access the relationship and will return an empty Collection.

So to make it work we need to change the controller as well:

Now that comments are eager-loaded the Resource will include them in the resulting array. And now the API has only two database queries:

select * from posts
select * from comments where post_id IN (...)

And that's it! Now this API has a much better performance and runs much less database queries.

Fortunately, if you're using Laracheck you don't have to worry about these kinds of mistakes! It'll take care of these and warn you about missing whenLoaded() calls.

Missing Database Indices in Migrations

One of the main reasons for slow database queries is missing or wrong indices. So first, we need to understand what is an index. Under the hood, MySQL stores tables in files. So every time you make a query it needs to fetch the data from the filesystem. Which is slow. This is why indices exist.

When you add a B-TREE (balanced tree) index to your table MySQL will load the column's content into a tree data structure and it doesn't have to fetch all the rows in a sequential way. And this tree in ordered. This is important, since searching in an ordered dataset is much more efficient then searching in an unordered dataset. Just think about binary search, for example.

Here's a balanced tree:

It has three pretty important rules:

• The left subtree always contains nodes that are less than the root node. For example, 2 and 4, 1 and 2, 5 and 6
• The right subtree always contains nodes that are greater then the root node. For example, 6 and 4, 7 and 6 or 3 and 2
• The leaf nodes always has the same depth. You can see a 3-level B-TREE in the image where each subtree has the same depth. This is no coincidence. This is why it's called balanced.

If you want to insert the number 8 into this tree, you cannot do that:

This tree is not balanced. The most right subtree is deeper than the others which means it's finding number 8 takes more iteration and more time. To keep the tree balanced we need to do something like this:

Remember, the "B" stands for "balanced" not "binary"!

All right, now back to reality. Gimme some Laravel code:

When you add an index such as published_at MySQL will store the dates in the B-TREE itself (in memory). Which makes queries pretty fast, because the DB engine can search in the sorted, balanced tree compared to fetching everything in a linear fashion from the filesystem. Now indexing is a pretty complex topic but these are the basics. If you miss an important index it can literally set your servers on fire.

If you want to eliminate these kinds of performance issues from your project, register a free trial to Laracheck and it'll warn you whenever they occur.