Transactions and query speed
Eloquent is convenient but hides queries: one template line can run a hundred database calls. This lesson is about spotting such places, processing large tables and running several queries as one unit.
The N+1 problem
$posts = Post::all();
foreach ($posts as $post) {
echo $post->author->name; // a query for every post
}A hundred posts mean a hundred and one queries. Load the relationship in advance:
$posts = Post::with('author')->get(); // two queriesForbidding lazy loading
So that such places do not go unnoticed, turn on a check — during development lazy loading raises an error:
use Illuminate\Database\Eloquent\Model;
public function boot(): void
{
Model::preventLazyLoading(!app()->isProduction());
}Counting related records
To show the number of comments there is no need to load the comments themselves:
$posts = Post::withCount('comments')->get();
foreach ($posts as $post) {
echo $post->comments_count;
}The database counts, not PHP
Post::all()->count(); // loads every record into memory
Post::count(); // one SELECT COUNT(*) query
Post::where('user_id', 5)->exists(); // instead of count() > 0
Post::sum('views');
Post::max('created_at');Only the needed columns
$titles = Post::select('id', 'title')->get();
$emails = User::pluck('email');A list of titles does not need the long text of each post.
Large tables
Post::all() for a million records exhausts memory. Records are processed in chunks:
User::chunkById(500, function ($users) {
foreach ($users as $user) {
// processing
}
});
foreach (User::lazy() as $user) {
// one model at a time, queries run in portions
}Transactions
Several queries that must all succeed or all fail are wrapped in a transaction:
use Illuminate\Support\Facades\DB;
DB::transaction(function () use ($from, $to, $amount) {
$from->decrement('balance', $amount);
$to->increment('balance', $amount);
Transfer::create(['from_id' => $from->id, 'to_id' => $to->id, 'amount' => $amount]);
});If an exception occurs inside, every change is rolled back.
Locking a row
Two simultaneous withdrawals may read the same balance. lockForUpdate() makes the second query wait:
DB::transaction(function () use ($id, $amount) {
$account = Account::lockForUpdate()->find($id);
if ($account->balance < $amount) {
throw new InsufficientFunds();
}
$account->decrement('balance', $amount);
});Indexes
Columns used for searching and ordering should have an index:
$table->index('user_id');
$table->index(['status', 'created_at']);The foreignId()->constrained() method creates an index by itself.
Common mistake
Optimising at random is useless. First look at which queries run: install Laravel Debugbar or Telescope — they show a page's list of queries and the time of each. A slow page is usually caused not by one "heavy" query but by a hundred identical light ones.