Making Queries Fast
Select fewer columns, index what you filter on, write in batches, and read the query plan before changing anything.
Order::select(['id', 'user_id'])->where('status', 'paid')->chunkById(500, $callback, column: 'id')A slow page is usually one slow query, or one query run too many times. Four moves cover most of it: ask for less, index what you filter on, write in batches, and look at the plan instead of guessing.
Ask for less
select() limits the columns. Order::select(['id', 'user_id', 'total']) and Order::select('id', 'user_id', 'total') are the same call in two forms. Use withCount('items') rather than loading every item in order to count it, and paginate() rather than all().
Index what you filter on
An index is a sorted structure over one or more columns that the database can search without reading the whole table. You add one in a migration: $table->index(['user_id', 'created_at']). Column order matters. That index serves where user_id = ? order by created_at desc; it does not serve a search on created_at alone. Schema::getIndexes('orders') lists what a table already has.
Write in batches
chunk(500, $callback) walks a large result a page at a time so only 500 models are in memory. chunkById(500, $callback) pages on the primary key rather than an offset, so rows cannot be skipped or repeated when the loop changes the rows it is filtering on.
Read the plan
explain() runs EXPLAIN on your query and returns the plan as a collection. dumpRawSql() prints the SQL with its bindings substituted, so you can run it in a database client by hand.
Example
<?php
// database/migrations/2026_03_11_094500_add_user_created_index_to_orders_table.php
declare(strict_types=1);
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// Generated by:
// php artisan make:migration add_user_created_index_to_orders_table --table=orders
return new class extends Migration
{
public function up(): void
{
Schema::table('orders', function (Blueprint $table): void {
// Serves: where user_id = ? order by created_at desc
$table->index(['user_id', 'created_at']);
});
}
public function down(): void
{
Schema::table('orders', function (Blueprint $table): void {
// The array form drops the conventionally named index,
// orders_user_id_created_at_index.
$table->dropIndex(['user_id', 'created_at']);
});
}
};When to use it
- Backfilling a column across a million-row table without exhausting memory.
- Adding the index behind a report that filters by account and sorts by date.
- Trimming a list endpoint that returns 40 columns down to the five the client renders.
- Checking whether a query really uses the index you added for it.
More examples
A backfill that does not load the table
Generated by php artisan make:command BackfillOrderTotals. In Laravel 13 the generator writes the #[Signature] and #[Description] attributes; the older longhand properties, protected $signature = 'orders:backfill-totals'; and protected $description = '...';, still work, and the attribute wins if a class carries both. chunkById pages with `where id > <last id>` instead of an offset, so inserts and updates during the run cannot shift the window. The `items:id,order_id,price,quantity` form eager loads four columns only - and includes order_id, without which the relation could not be matched.
<?php
// app/Console/Commands/BackfillOrderTotals.php
declare(strict_types=1);
namespace App\Console\Commands;
use App\Models\Order;
use App\Models\OrderItem;
use Illuminate\Console\Attributes\Description;
use Illuminate\Console\Attributes\Signature;
use Illuminate\Console\Command;
use Illuminate\Database\Eloquent\Collection;
#[Signature('orders:backfill-totals')]
#[Description('Recalculate the stored total on every paid order')]
class BackfillOrderTotals extends Command
{
public function handle(): int
{
Order::query()
->where('status', 'paid')
->with('items:id,order_id,price,quantity')
->chunkById(500, function (Collection $orders): void {
foreach ($orders as $order) {
$order->update([
'total' => $order->items->sum(
fn (OrderItem $item) => $item->price * $item->quantity
),
]);
}
}, column: 'id');
return self::SUCCESS;
}
}Looking at the SQL and the plan
explain() and dumpRawSql() live on the query builder, and Eloquent passes both straight through, so they return the plan collection and the printed SQL rather than the Eloquent builder. On MySQL, read the plan's `key` column for the index actually chosen and `rows` for how many rows the optimiser expects to examine: a `key` of null on a large table means a full scan. Read it before and after adding an index, on data the size of production - on a thousand rows every plan looks fine.
<?php
use App\Models\Order;
$query = Order::query()
->select(['id', 'user_id', 'total', 'created_at'])
->where('user_id', 42)
->latest();
// The SQL with every binding substituted, ready to paste into a client.
$query->dumpRawSql();
// The database's own plan, as an Illuminate\Support\Collection of rows.
$plan = $query->explain();Counting and existence without loading rows
Counting and checking are the two cases where loading the relationship is pure waste. withCount() adds a correlated subquery to the parent query, so the count costs no extra round trip, and the attribute name is the relationship name plus _count. Use this on list pages that show "12 orders" next to each row - the alternative is an N+1 that also hydrates every related model.
<?php
use App\Models\User;
// One subquery per relationship, no orders loaded.
// Each model then exposes an integer $user->orders_count.
$users = User::query()
->withCount('orders')
->paginate(25);
$user = User::findOrFail(1);
// exists() asks the database and stops at the first matching row.
// $user->orders->isNotEmpty() would fetch every order to answer the same
// question.
if ($user->orders()->exists()) {
// ...
}
Discussion