Data and performance

Without an index, MySQL finds a row by reading every row in the table and checking each one, so the query gets slower as the table grows. With an index it reads a few pages of a sorted tree and then the one row it needs.

I decide the indexes while I design the schema and write them into the migration: a unique constraint on each natural key, composite indexes for columns that are queried together, and foreign keys between tables.

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('customer_id')->constrained();
    $table->string('reference')->unique();
    $table->string('status');
    $table->timestamps();

    $table->index(['status', 'created_at']);
});

A page lists 40 posts and shows the author of each. If the authors are not loaded up front, Laravel fetches them one at a time as the template asks for them: one query for the posts, then one more for every post. Eager loading fetches all the authors in a single second query.

I load relations with with(), loadMissing() and withCount(). In recent projects I also switch on preventLazyLoading() everywhere except production, so an N+1 query throws an exception while I am developing and cannot reach production unnoticed.

// AppServiceProvider::boot()
Model::preventLazyLoading(! $this->app->isProduction());

Calling get() on a big table builds every row as an object before the first one is processed, and on a big enough table that runs out of memory. For scheduled commands and backfills I use chunkById(), which reads a batch, processes it and lets it go before reading the next.

Inside queued jobs that assemble a large output I use lazy(). It reads in batches underneath but hands my code one record at a time, so each record can be written out as it arrives.

// A scheduled command or a backfill
Order::where('status', 'pending')
    ->chunkById(2000, function ($orders) {
        $orders->each->recalculate();
    });

// A queued job writing a large file
foreach (Order::with('customer')->lazy() as $order) {
    $writer->addRow($order->toExportRow());
}

Files from other systems arrive with stray spaces, mixed case and repeated rows, and an import sometimes has to be run again after it has half finished. I trim and lower-case each identifier, then match the row to an existing record by its natural key, so importing the same file again creates no duplicates.

Each row is validated on its own and the import keeps a status for it, so one bad row is reported with its reason and the rest still go in. Large spreadsheets are read in chunks.

$member = Member::updateOrCreate(
    ['email' => Str::lower(trim($row['email']))],
    ['name' => Str::squish($row['name'])],
);

$status = match (true) {
    $member->wasRecentlyCreated => 'created',
    $member->wasChanged() => 'updated',
    default => 'unchanged',
};

Some values must be given out exactly once, such as the next sequential reference number. If two requests read the last number at the same moment, both add one and both write the same value.

Where two requests could collide like this, I wrap the read and the write in a database transaction and lock the row with lockForUpdate(). The second request then waits until the first has committed, and reads the new value. I use the same approach when publishing a draft.

$reference = DB::transaction(function () {
    $counter = Counter::where('name', 'order_reference')
        ->lockForUpdate()
        ->first();

    $counter->increment('value');

    return $counter->value;
});

Two more habits, without demonstrations. Aggregates that are expensive to compute and read often go behind Cache::remember() with a time limit, so they are worked out once and reused until it runs out.

Queued work runs on named queues, so heavy jobs such as building a large document cannot hold up quick ones such as notifications. Expensive jobs are marked unique, so the same one cannot be queued twice.

$totals = Cache::remember(
    'orders.totals',
    now()->addMinutes(10),
    fn () => Order::totalsByStatus(),
);

// Heavy work has its own queue, and cannot be queued twice
class BuildDocument implements ShouldQueue, ShouldBeUnique
{
    use Queueable;

    public function __construct(public Document $document)
    {
        $this->onQueue('documents');
    }

    public function uniqueId(): string
    {
        return (string) $this->document->id;
    }
}