Optimizing Laravel Performance: Addressing Slowdowns with Large Datasets

I am currently working on a project using Laravel, and I have encountered performance issues as the dataset grows. Specifically, my application is experiencing noticeable slowdowns, particularly when interacting with a table containing approximately 23,000 entries—this is the lead table.

As new data continues to accumulate, I find that the application often becomes unresponsive, requiring significant wait times for actions that should be executed promptly.

I am seeking guidance on strategies or best practices to optimize the performance of my Laravel application in this context. Any suggestions regarding database optimization, indexing, or caching techniques would be greatly appreciated. Thank you for your assistance!

Comments

[Enric Puigdemont]

In order of priority:

Eloquent Query optimization

When interfacing with any table, ensure that the Eloquent queries are optimized, lazy and eager loading is used when necessary, and that queries are not incurring N+1.
You can use Laravel Debugbar to inspect the whole db query trace, you will see which queries are repeated many times and especially which queries are slow.

I can't stress this part enough, 9/10 issues with performance I've encountered could be solved in a few minutes after inspecting the queries with Debugbar. Badly optimized queries have a cascading effect, as they slow down your entire database and any further queries. In some cases I've cut down a page loading in 10 seconds to less than 150 milliseconds by eager loading the required Eloquent relationships and limiting which columns are selected.

In this example, the Post model has a relationship with the User model, each post has one author which is an instance of the User model.
If you want to get the email from the author of each post, by default if you do this, you'll be incurring an N+1 query. Where first you'll query the database for the posts. Then when going through each post, you will execute another query to get the email attribute from User.

In the Post model:

public function author()
{
    return $this->hasOne(User::class, 'author_id', 'id');
}

$posts = Post::where('type', 'someType')->get();
foreach ($posts as $post) {
    $commentText = $post->author->email;
}

If you instead apply eager loading by indicating the relationship when obtaining the posts, Eloquent will do this in a single query instead:

$posts = Post::with('author')->where('type', 'someType')->get();
foreach ($posts as $post) {
    $email = $post->author->email;
}

It can be very helpful to read the Relationships section of the documentation.
There's an entire section of the Laravel documentation dedicated solely to Eloquent for a good reason, how you manage interfacing with your database can be the difference between a slow, hard to debug application to a blazing fast responsive one.

Also, non-database but relevant. When using Livewire, applying lazy loading to anything that doesn't need to be shown immediately also speeds up page loading significantly and makes the application feel more responsive.

Deferring slow/bulk operations to Queued Jobs

If significant portions of your application involves writing large datasets to your database, you can defer that writing/updating to queued Jobs, this won't necessarily speed up the database itself, but will make the application more responsive by not having your website wait for database operations to finish.

For example, if you have a page with a button that creates multiple entries into a table, updates an existing entry on another, and doing so requires reading from 3 other tables.
When the user clicks the button the application will become unresponsive until that finishes.
Instead, you can dispatch a job that performs all those operations when the user presses the button, and show the user a message that it's being processed.

Database schema: Foreign Keys and Indexes

Is the database schema setup correctly?
Are you using foreign keys?
Are you using indexes when applicable?
Indexing speeds up reads but slows down inserts, so keep that in mind.
Creating too many indexes will degrade rather than improve performance, so do this with care. Most of the time simply indexing the primary key of the most queried tables is enough.

Refactoring Code

How is the code organized?
Are you querying for the same thing in 5 different places for the same request?
Avoid querying for more data than you actually need, for example if you only need to retrieve 2 columns from a table to do what you need, using ->select() in your Eloquent query will limit to the columns specified, this can have a huge impact if a table has many columns or columns that contain large amounts of data.

Refactoring large pieces of code to avoid re-querying things can be a large time investment for a smaller return than just fixing the slow database queries.

Caching

If you have a dataset that doesn't change often (once or twice a day) but is accessed frequently in your application, caching that data with a lifetime of hours can speed things up. Whenever the data changes (ie: model is updated) recreate the cache entry.
If you have a dataset or query that changes more frequently, you can cache it for a shorter duration, like a minute or less.

Laravel also offers the array cache driver, which will cache something only for the duration of the request. If a dataset is used throughout a request and is being queried multiple times, caching it for the duration of that request can help.

Caching things can quickly become a crutch and make things harder to debug and inspect, as you're not longer using data from the database, but another storage entirely, use it sparsely and only when it makes a significant difference in performance.

A common problem I've found with caching things carelessly is to cache some data that affects the outcome of something else, the database is updated but the cache is not. Finding that the cache being outdated is the culprit for unexpected behavior can be difficult, especially in production environments where you can't easily just dump and log to check.

Here's a use case I have where caching is very effective:

A list that is loaded from data retrieved from an external API through an HTTP request.

In our application database we have multiple tables where contextual data is stored to compliment the data from the external API that the user needs to see. This data has its own set of relationships. This data changes only on deployments and when an administrator edits something in a relevant admin page. Whenever the data changes we recreate the cache entries.

The data returned from the API is minimal, and many of the elements in the API dataset are shared between items, some information is not present in the initial API dataset and requires another HTTP request to the API to retrieve the relevant ids.

Instead of querying the contextual tables each time for each item in the list, and having to send multiple HTTP requests to the external API, we cached the data from these tables.
Some extra HTTP requests that are unavoidable are instead only executed when the user clicks the show more button.

The end result is going from an initial loading of upwards of 30 seconds and reaching timeouts often, and search being sluggish or flat out not working, to the page loading in under 200 milliseconds and search being faster than that. The bottleneck is now the response time of the external API on a single HTTP request, rather than database queries.