Laravel Magazine
MySQL Query Optimization Tips Every Laravel Developer Should Know
MySQL Query Optimization Tips Every Laravel Developer Should Know
Here's a hard truth: Eloquent is not slow. Bad usage of Eloquent is slow. The ORM is doing exactly what you tell it to do -- it's just that sometimes what you're telling it to do is absolutely wild.
These tips are the practical ones. The ones that show up in real codebases, cause real pain, and have real fixes. Let's get into it.
1. Slay the N+1 Monster with Eager Loading
The N+1 query problem is the most common performance killer in Laravel apps. It happens when you loop over a collection and trigger a new query for each item.
// BAD: This fires 1 query for users, then 1 per user for their posts
$users = User::all();
foreach ($users as $user) {
echo $user->posts->count();
}
Fix it with eager loading using with():
// GOOD: 2 queries total, no matter how many users
$users = User::with('posts')->get();
foreach ($users as $user) {
echo $user->posts->count();
}
If you want to count related records without loading them all into memory, use withCount():
$users = User::withCount('posts')->get();
foreach ($users as $user) {
echo $user->posts_count; // pre-loaded, no extra queries
}
2. Select Only What You Actually Need
By default, Eloquent runs SELECT *. On tables with many columns -- especially ones with TEXT or JSON
blobs -- that's a lot of data being hauled across the wire for no reason.
// BAD: Retrieves every column including bio, metadata, avatar_url, etc.
$users = User::all();
// GOOD: Only get what you need
$users = User::select(['id', 'name', 'email'])->get();
This matters even more when you're loading relationships. Constrain those too:
$users = User::with(['posts:id,user_id,title'])->select(['id', 'name'])->get();
Note the format for constrained eager loads: relationship:column1,column2. Always include the foreign
key or the relationship won't hydrate correctly.
3. Add Indexes to Your Migrations
Proper indexing can improve query speed by 50-80% on large tables. If you're running WHERE, ORDER BY,
or JOIN operations on a column frequently, that column needs an index.
// In your migration
Schema::table('posts', function (Blueprint $table) {
$table->index('user_id'); // Single column index
$table->index(['status', 'created_at']); // Composite index
});
Composite indexes are ordered. An index on ['status', 'created_at'] helps queries that filter by
status alone, or status AND created_at together -- but not created_at alone. Plan your indexes
around your actual query patterns.
4. Use chunk() and chunkById() for Large Datasets
Processing thousands of records with ->get() loads everything into memory at once. That's a quick way to
exhaust your server's RAM. Use chunk() instead:
// BAD: Loads 100,000 records into memory
User::where('active', true)->get()->each(function ($user) {
// process...
});
// GOOD: Processes 500 at a time
User::where('active', true)->chunk(500, function ($users) {
foreach ($users as $user) {
// process...
}
});
For even better performance, prefer chunkById(). It avoids issues with records shifting position during
chunking (which can cause chunk() to skip records on tables being modified during processing).
User::where('active', true)->chunkById(500, function ($users) {
foreach ($users as $user) {
// process...
}
});
5. Use cursor() for Streaming Large Results
When you need to iterate over a huge result set but don't need all records simultaneously, cursor() is
your friend. It uses PHP generators to stream results one at a time, dramatically reducing memory usage.
foreach (User::where('active', true)->cursor() as $user) {
// Only one model in memory at a time
ProcessUser::dispatch($user);
}
cursor() is ideal for read-only operations. For operations that write back to the database, chunkById()
is generally safer.
6. Debug Your Queries with toRawSql()
When something is slow, the first step is seeing what SQL is actually being generated. Laravel 10.15+
added toRawSql() which gives you the full query with bindings already substituted in:
$query = User::where('active', true)
->with('posts')
->orderBy('created_at', 'desc');
// See the raw SQL with all bindings filled in
dd($query->toRawSql());
Pair this with DB::listen() during development to log every query your application fires:
// Add to AppServiceProvider::boot()
if (app()->isLocal()) {
DB::listen(function ($query) {
logger()->info($query->sql, ['bindings' => $query->bindings, 'time' => $query->time]);
});
}
Once you can see what's happening, optimizing becomes a lot more straightforward.
7. Wrap Batch Inserts/Updates in Transactions
Each individual insert or update acquires and releases a lock on the table. In MySQL, wrapping a batch of operations in a single transaction means the lock is acquired once, dramatically reducing overhead.
// BAD: Individual queries, each with their own lock overhead
foreach ($records as $record) {
User::create($record);
}
// GOOD: Single transaction, single lock acquisition
DB::transaction(function () use ($records) {
foreach ($records as $record) {
User::create($record);
}
});
For bulk inserts where you don't need model events to fire, User::insert([...]) bypasses Eloquent
entirely and is even faster.
Putting It Together
None of these are magic. They're just habits. Add withCount() instead of loading collections to count
them. Throw indexes on your frequently-queried columns. Use toRawSql() when something feels slow before
you start guessing.
Your database is fast. Help it stay that way.
Sources: