DEV Community: UCodeSoft

The Index Won't Save You: Debugging a Slow Derived Table in MySQL

UCodeSoft — Mon, 20 Jul 2026 12:54:03 +0000

Twenty rows. Over 500ms. That's the kind of mismatch that makes you stop what you're doing and open EXPLAIN. Our team ran into exactly this while working on a client's Laravel application, and it's a good enough example that we wanted to share the process.

Before we dive in

If you're already comfortable with EXPLAIN, derived tables, and correlated subqueries, skip ahead to the query. If not, here's everything you need going in.

EXPLAIN shows you MySQL's intended plan for a query before it runs: which indexes it's considering, roughly how many rows it expects to touch, and an Extra column that flags trouble. Two phrases matter here: Using filesort means it had to sort manually because no index gave it the order for free, Using temporary means it had to build a scratch table to hold intermediate results.

A derived table is a subquery in the FROM clause, wrapped so it acts like its own temporary table for the rest of the query. MySQL sometimes has to fully build that table before it can apply any filtering from the outer query, that disconnect is the core problem in this post.

A correlated subquery references a column from the surrounding query, so it can't be evaluated once for the whole statement, it has to run per row. Where you place it matters a lot: inside a JOIN, it may resolve for every joined row before anything else happens. In the SELECT list, MySQL can wait until WHERE and LIMIT have already narrowed things down first.

GROUP_CONCAT with ORDER BY is grouped, ordered string aggregation, folding many rows into one value per group in a specific order. That ordering requirement is why it needs a temp table, no index gets around it.

A covering index contains every column a query needs, so MySQL can answer from the index alone without a second trip back to the table row.

The setup

Here's the query, using a generic schema so it's easy to map onto your own. A task-management system, where every assignment has "fields," dynamic values tied to specific steps in a template, but only the steps before that template's first decision branch.

Here's how the relevant tables connect:

SELECT
  Assignment.id,
  Assignment.status,
  Assignment.modified,
  Fields.field_name,
  Fields.field_value
FROM assignments AS Assignment
LEFT JOIN (
  SELECT
    AF.assignment_id,
    GROUP_CONCAT(FieldDef.name ORDER BY Step.sort_order) AS field_name,
    GROUP_CONCAT(AF.value ORDER BY Step.sort_order) AS field_value
  FROM assignment_fields AF
  INNER JOIN template_steps Step ON Step.id = AF.step_id
  INNER JOIN (
    SELECT template_id, MIN(sort_order) AS first_decision
    FROM template_steps
    WHERE step_type = 'decision'
    GROUP BY template_id
  ) AS decision_start ON decision_start.template_id = Step.template_id
  INNER JOIN field_defs FieldDef ON FieldDef.id = AF.field_def_id
  WHERE Step.sort_order < decision_start.first_decision
  GROUP BY AF.assignment_id
) AS Fields ON Fields.assignment_id = Assignment.id
WHERE Assignment.team_id = 58
  AND Assignment.status = 'pending'
ORDER BY Assignment.modified DESC
LIMIT 20;

Step 1: EXPLAIN shows two separate problems

Not one problem, two, stacked on top of each other.

The outer scan on Assignment was filesorting on modified after filtering. The index in use didn't cover the sort column.
The derived table (Fields) was scanning its own inner decision_start subquery cold, flagged with Using temporary; Using filesort.

Treat every row of EXPLAIN as its own issue. One big index thrown at the whole query rarely fixes a compound problem like this.

Here's roughly what that looked like in the plan itself:

Step 2: index the inner subquery

decision_start computes MIN(sort_order) grouped by template_id, filtered on step_type = 'decision'. Unindexed, that's a full scan and sort of every step in every template.

CREATE INDEX idx_decision_lookup
  ON template_steps (step_type, template_id, sort_order);

Now the grouped MIN() reads straight off the index in order. No temp table, no filesort, for that piece.

Step 3: cover the join columns

Inside the derived table, the joins on AF and FieldDef were each doing an index lookup followed by a row lookup for value. A covering index removes the second trip.

CREATE INDEX idx_af_covering
  ON assignment_fields (step_id, assignment_id, field_def_id, value);

Small per-row win, but it's inside logic that reruns per group, so it adds up fast.

Step 4: fix the outer filesort

Assignment had (team_id, status) indexed, but nothing covering ORDER BY modified. Extending the index let MySQL walk it pre-sorted and stop at LIMIT 20, instead of sorting the whole filtered set afterward.

ALTER TABLE assignments
  DROP INDEX idx_team_status,
  ADD INDEX idx_team_status (team_id, status, modified);

Four legitimate, measurable wins. Query still slow.

The ceiling indexing can't get past

Fields has GROUP BY AF.assignment_id with an ordered GROUP_CONCAT inside it. In MySQL, grouped ordered string aggregation has to materialize a temp table, there's no index that skips that. Index every join feeding into it and the aggregation step will still show Using temporary; Using filesort, because that's inherent to the operation.

The bigger issue: this derived table has zero awareness of team_id = 58. It doesn't run after the outer WHERE narrows things down, it's built first, aggregating fields for every assignment in the system, and only afterward joined down to the 20 rows you wanted. Its cost scales with total row count, not your filtered result. Indexing speeds up how each row is read on the way in, it can't stop the aggregate from running for rows you're about to throw away.

Side note: the optimizer got weird after ALTER TABLE

Adding modified to the composite index should have been a clean win. Instead, EXPLAIN showed MySQL dropping the composite index entirely for index_merge, intersecting old single-column indexes, filesort and all.

ANALYZE TABLE assignments; didn't change anything on its own. What worked was removing the alternative:

ALTER TABLE assignments
  DROP INDEX idx_status,
  DROP INDEX idx_team_only;

With the redundant single-column indexes gone, index_merge wasn't an option anymore, and the composite index got used as intended. Redundant indexes cost more than write overhead, they give the optimizer worse choices, and it sometimes takes them.

The actual fix: bound the aggregate to the filtered rows

Two ways to do this. They're not equivalent.

Option A, correlated subquery in the join. Looks reasonable, often isn't:

LEFT JOIN (
  SELECT AF.assignment_id, GROUP_CONCAT(...) AS field_value
  FROM assignment_fields AF ...
  WHERE AF.assignment_id = Assignment.id
) AS Fields ON ...

MySQL 5.7 often runs this as a dependent join, re-executing the inner query once per outer row via a join buffer. Can end up slower than the original derived table, since you've traded one big aggregation for many small dependent ones with no early cutoff benefit.

Option B, correlated scalar subqueries in the SELECT list. This one works:

SELECT
  Assignment.id,
  Assignment.status,
  Assignment.modified,
  (
    SELECT GROUP_CONCAT(FieldDef.name ORDER BY Step.sort_order)
    FROM assignment_fields AF
    INNER JOIN template_steps Step ON Step.id = AF.step_id
    INNER JOIN field_defs FieldDef ON FieldDef.id = AF.field_def_id
    WHERE AF.assignment_id = Assignment.id
      AND Step.template_id = Assignment.template_id
      AND Step.sort_order < (
        SELECT MIN(sort_order) FROM template_steps
        WHERE template_id = Assignment.template_id AND step_type = 'decision'
      )
  ) AS field_name
  -- field_value mirrors the same shape
FROM assignments AS Assignment
WHERE Assignment.team_id = 58 AND Assignment.status = 'pending'
ORDER BY Assignment.modified DESC
LIMIT 20;

A scalar subquery in the SELECT list runs per output row, after WHERE has narrowed things, and after LIMIT has already decided which rows need it at all. MySQL can often stop once it has its 20 rows. A join-based correlated subquery gets no such benefit, it resolves for every row the join touches before LIMIT is applied.

Same core idea, per-row correlation instead of a global aggregate, very different execution shape.

Takeaways

Treat every EXPLAIN row as its own problem. A slow query can have several unrelated causes stacked together, fixing one won't even register in the timing until the rest are gone too.
Grouped, ordered aggregation (GROUP_CONCAT(... ORDER BY ...) GROUP BY x) always materializes. No index removes that, it's a property of the operation.
A derived table with GROUP BY is a wall the optimizer can't see through, your outer WHERE filters won't get pushed into it no matter how selective they are.
Redundant single-column indexes aren't free even when unused for writes, they widen the optimizer's (sometimes worse) options.
"Correlated subquery" isn't a single technique, where you put it (JOIN vs SELECT list) decides whether LIMIT can help you.

Indexing gets you most of the way there, right up until the operation itself is the bottleneck. At that point the fix isn't a better index, it's changing what you're actually asking MySQL to compute.

We work through problems like this regularly on Laravel and MySQL systems at scale. If you've hit a similar wall, drop a comment, we're always up for talking through a gnarly query.

We also cover this kind of debugging in more depth over on our Substack: https://ucodesoft.substack.com/p/the-index-wont-save-you-a-slow-query

The "Laravel Isn't Secure Enough for Enterprise" Myth is Dead

UCodeSoft — Thu, 16 Jul 2026 16:46:15 +0000

Every backend engineer has heard it: "Laravel is great for MVPs and small projects, but it’s just not secure enough for enterprise scale."

Usually, this critique comes from people who haven't looked at the modern ecosystem, or who are judging the framework based on a poorly configured legacy codebase they inherited from a junior team.

The reality? Laravel bakes in more native security defaults than most backend frameworks require you to configure manually. If an application suffers from basic vulnerabilities like SQL injection or Cross-Site Scripting (XSS), it is almost always a result of explicit implementation choices—not framework limitations.

As Certified Laravel Partners (https://laravel.com/partners/ucodesoft), doing deep-dive security audits is a standard baseline for every enterprise migration and build we take on. Here is what Laravel provides out of the box, and how to leverage it properly at scale.

1. Hardened Front-End Defaults (XSS & CSRF)

Many frameworks leave Cross-Site Request Forgery (CSRF) and Cross-Site Scripting (XSS) up to manual configuration or third-party middleware. Laravel treats them as non-negotiable baselines:

Blade Output Escaping: The {{ $variable }} syntax automatically routes data through PHP’s htmlspecialchars function. To introduce an XSS vulnerability, a developer must intentionally use raw tags ({!! $variable !!}).
Mandatory CSRF: Every state-changing HTTP request (POST, PUT, DELETE) is intercepted by native CSRF middleware.

2. First-Party API Authentication

Enterprise apps require robust, scalable token authentication. Instead of gambling on unmaintained open-source packages, Laravel provides two native, first-party, continuously patched solutions:

Laravel Sanctum: Lightweight token/cookie authentication perfect for SPAs and mobile apps.
Laravel Passport: A full OAuth2 server implementation built on top of the League OAuth2 server package.

Because these are first-party utilities, they integrate seamlessly with the framework's authentication guards and receive immediate security patches.

3. Native Protection Against SQL Injection

SQL injection remains a massive threat to enterprise databases. Laravel’s Eloquent ORM and Query Builder mitigate this entirely by using PDO parameter binding for all database operations.

Input data is bound to parameters rather than being directly concatenated into raw SQL strings. A developer has to explicitly bypass Eloquent and deliberately misuse raw queries (e.g., DB::raw()) to expose the database.

4. Isolated, Testable Authorization Policies

In legacy enterprise codebases, permission checks often become a tangled mess of if/else statements scattered across controllers and views. This fragmentation makes it incredibly easy to miss a check.

Laravel solves this architecturally through Policies. By isolating authorization rules into discrete, dedicated classes mapped to specific models, you can unit-test your permission logic in complete isolation:


php
// Clean, isolated, and highly testable enterprise authorization
public function update(User $user, Order $order)
{
    return $user->id === $order->user_id
        ? Response::allow()
        : Response::deny('You do not own this order.');
}

**Security Requires Configuration Discipline**

A framework can provide the most secure foundation in the world, but it still requires team discipline to maintain. In our enterprise engagements, the most critical vulnerabilities we uncover are rarely framework flaws. They are human errors:

* Leaving `APP_DEBUG=true` in production environments.
* Broad, insecure CORS header configurations (`*`).
* Failing to rotate or protect the `APP_KEY`.

Laravel doesn't eliminate the need for skilled, disciplined engineers—but it ensures your team doesn't waste hundreds of hours reinventing basic security compliance.

**Let's discuss in the comments:**
If you've audited an inherited Laravel codebase at scale, what was the most terrifying security configuration oversight you uncovered?

*Need an architectural or security review for your application? Get in touch with our team at (https://ucodesoft.com/contact-us).*

Seven things production taught me that no tutorial did

UCodeSoft — Tue, 14 Jul 2026 16:57:56 +0000

I spent the last few months building out a shipping integration on top of Shippo, and along the way, I kept running into the same lesson: the "quick" way to write Laravel code almost always works — right up until it doesn't. This isn't a theory post. Every pattern below came from something that actually broke, or almost broke, in this codebase.

Here are seven things I'd tell a past version of myself before writing another shipment controller.

1. Stop passing raw arrays to third-party APIs.
The ticket says "wire up Shippo shipment creation." The fastest way to close it is to grab a few fields off the order, throw them into an array, and fire it at the HTTP client. It works on the first try. It works in the demo. So what's wrong with it?

Nothing — until six months from now, when a new dev who's never opened Shippo's docs calls your method, forgets that parcels need a distance_unit key, and finds out the hard way via a 422 in staging. The array approach doesn't fail where the mistake happens. It fails at the network boundary, with a stack trace pointing at your HTTP client instead of the line that actually got it wrong.

My first instinct was "Shippo owns this shape, so a DTO is just re-describing their API." That's backwards. The DTO isn't there to constrain Shippo — it's there to constrain me. It makes it structurally impossible to send Shippo something malformed, and it gives the next person a single file that answers "what does a shipment request actually need?"

phpfinal readonly class ShippoParcel
{
public function __construct(
public float $lengthCm,
public float $widthCm,
public float $heightCm,
public float $weightKg,
) {}

public function toPayload(): array
{
    return [
        'length' => (string) $this->lengthCm,
        'width' => (string) $this->widthCm,
        'height' => (string) $this->heightCm,
        'distance_unit' => 'cm',
        'weight' => (string) $this->weightKg,
        'mass_unit' => 'kg',
    ];
}

}

toPayload() is doing something specific: it's the one place in the codebase that knows Shippo wants distance_unit and mass_unit as separate string keys instead of floats. That's a Shippo quirk, not a domain concept, and it deserves to live exactly once — at the edge — instead of being copy-pasted into every place that builds a parcel array.

Do the same thing on the response side. If a successful transaction contains a tracking_number buried three levels deep, don't make every consumer memorize that. Wrap it in a response DTO and throw a domain exception right there if a field you depend on is missing, instead of discovering it three calls later when something null-derefs.

2. Contextual binding: stop putting environment checks inside your classes.

Most of the time, one global container binding is exactly right. Don't reach for contextual binding before you need it — that's its own flavor of overengineering. But there's a specific smell that tells you when you've outgrown it: a service class that checks config() or does an instanceof check to decide its own behavior. That class just took on a second job — deciding which implementation it should be — and that job belongs to the container.

Here's a real one from this project: staging needs to hit Shippo's actual sandbox API so QA can test the full flow. But the internal support tool — the one an ops person uses to reprint a lost label — needs live credentials even when it's deployed next to staging, because a support agent reprinting a real label needs a real tracking number. An APP_ENV check can't express that; both consumers live in the same deployment.

php$this->app->when(QaShipmentController::class)
->needs(ShippoClient::class)
->give(SandboxShippoHttpClient::class);

$this->app->when(SupportLabelController::class)
->needs(ShippoClient::class)
->give(ShippoHttpClient::class);

Both controllers just type-hint ShippoClient. Neither has an if branch. Neither can drift onto the wrong credentials because someone forgot an env check.

One thing I'd flag: when you use the closure form of give() to resolve something like a region-specific storage driver, make the unmatched case throw instead of silently falling back to a default region.

php$this->app->when(LabelArchiver::class)
->needs(StorageDriverInterface::class)
->give(function ($app) {
return match (config('services.region')) {
'eu' => $app->make(S3EuStorageDriver::class),
'us' => $app->make(S3UsStorageDriver::class),
default => throw new RuntimeException('Unconfigured region: no storage driver bound.'),
};
});

A misconfigured deployment failing loudly at boot is a five-minute fix. The same misconfiguration that silently archives EU customer data to a US bucket for three weeks is a very different conversation with legal.

3. API versioning: the database doesn't get an opinion

The tempting shortcut when a mobile endpoint needs to change: just change it, ship a new app version, tell stragglers to update. It's less work than standing up parallel routes and resources for what might be a one-field difference.

Here's why that doesn't survive contact with mobile clients: you can't force an update. A web deploy hits everyone within minutes. A phone with your app from fourteen months ago, sitting in a drawer half the year, hits your API tomorrow with the same confidence as someone on today's build. You can't patch that client — you can only decide, in advance, how long you're willing to keep talking to it.

The rule I hold myself to now: version at the boundary, never inside business logic. The second a version check shows up inside a service class, that check is going to outlive the version it was written for.

phpfinal class ShipmentController extends V1\ShipmentController
{
protected function resource(Shipment $shipment): JsonResource
{
return ShipmentResource::make($shipment);
}
}

If v2 only changes the response shape, don't fork the whole controller — fork the resource and let v2 inherit the rest. Duplicating the entire controller feels safer in the moment, but now every bug fix has to be applied twice by someone who has to remember both files exist.

And tell clients a version is dying using something machine-readable, not a changelog nobody on the client side reads:

php$response->headers->set('Deprecation', 'true');
$response->headers->set('Sunset', 'Wed, 01 Oct 2026 00:00:00 GMT');

Then actually count requests against the old version after your sunset date. That count is the only honest answer to "can we turn this off yet" — not a guess from support tickets.

The part that actually burns teams isn't the routing layer; it's migrations. Add new columns as nullable, write to both old and new during the transition, confirm traffic on the old version has actually dropped to zero, and only then drop the legacy column in a separate deploy. "Low" traffic isn't zero traffic — one enterprise customer's warehouse scanner still hitting v1 nightly is enough to turn a routine cleanup into an on-call incident.

4. Real-time dashboards: broadcasting demos beautifully and then lying to you

Before reaching for WebSockets at all — just poll. setInterval, a fetch call, done. For a lot of internal dashboards, that's genuinely the right answer, not a lesser one. Broadcasting earns its complexity when both the update volume and the number of watchers climb.

What the "add a trait, save a model, watch it update live" demo doesn't show you: broadcasts that silently never arrive, events that land out of order, and clients that missed a whole window of updates because their laptop slept for two minutes.

A shipment tracking dashboard hits all three because "label purchased" and "in transit" webhooks can land milliseconds apart, and normal network jitter is enough to flip that order by the time it reaches a browser. The fix is a version field that the client can compare against:

phppublic function broadcastWith(string $event): array
{
return match ($event) {
'updated' => [
'id' => $this->id,
'status' => $this->status,
'version' => $this->updated_at->getTimestampMs(),
],
default => [],
};
}
jsEcho.private(shipments.${shipmentId}).listen('.ShipmentUpdated', (e) => {
if (e.version <= (lastSeenVersion[e.id] || 0)) return; // stale, ignore
lastSeenVersion[e.id] = e.version;
updateDashboard(e.status, e.trackingNumber);
});

The one everybody skips: reconnects. A dropped socket has zero memory of what it missed — there's no replay buffer. On reconnect, refetch the current state from a plain REST endpoint before trusting the next broadcast. If you skip this, you'll eventually get a bug report that reads "the dashboard says pending but it actually shipped hours ago," and it'll take you a minute to realize it's not a broadcasting bug at all — it's a missing REST call.

5. N+1 queries hide behind conditionals, not in obvious loops

Nobody writes an obvious N+1 loop and misses it in review. The ones that ship are nested two or three levels deep inside a branch that only fires for a subset of rows:

php$shipments = Shipment::with('customer')->get();
foreach ($shipments as $shipment) {
if ($shipment->customer->isHighVolume()) {
// N+1 fires here — parcels were never eager loaded
$shipment->parcels->each(fn ($p) => $p->carrier->name);
}
}

with('customer') only reaches one level deep. This is invisible in review unless someone mentally traces every branch two relations deep, and invisible in local dev because your ten-seeded shipments run ten queries instead of ten thousand. It shows up as connection pool exhaustion in production, during your busiest hour — not as a comment in a PR.

The actual fix isn't "remember to eager load" — memory isn't an engineering control. Make the framework refuse to ship the mistake:

phpModel::preventLazyLoading(! $this->app->isProduction());
Model::handleLazyLoadingViolationUsing(function (Model $model, string $relation): void {
logger()->warning("Attempted to lazy load [{$relation}] on model [" . get_class($model) . "].");
});

Throw in local and staging, where a broken deploy costs nothing. Log-and-allow in production, where throwing on a lazy load would turn a performance bug into an outage on the spot. And enable this in your test suite too — a test that seeds exactly one shipment with one parcel will never trip an N+1, because there's nothing to multiply.

For the fix itself, reach for loadMissing() instead of sprinkling with() everywhere defensively. It costs nothing if the caller already did the right thing, and acts as a safety net at every controller/job boundary if they didn't.

6. Cache::flush() is a sledgehammer with a hair trigger

A carrier's negotiated rate changes, something needs invalidating, and Cache::flush() is sitting right there — one line, guaranteed correct, impossible to get subtly wrong. It also wipes every unrelated cached value in your app and triggers a stampede as every concurrent request misses at once and hits the database simultaneously.

Cache tags scope the blast radius to exactly what changed:

phpCache::tags(['rates', "carrier:{$carrierId}"])->put(
"rate.{$originZip}.{$destZip}", $rateQuote, now()->addHours(6)
);

Cache::tags(["carrier:{$carrierId}"])->flush();

Wire the flush to the model lifecycle so it's correct by construction instead of by every developer remembering to add an invalidation line next to every write:

phpprotected static function booted(): void
{
static::saved(function (CarrierAccount $account): void {
if ($account->wasChanged('negotiated_rate_table')) {
Cache::tags(["carrier:{$account->id}"])->flush();
}
});
}

Two things worth knowing before you build around this: cache tags require Redis or Memcached — the file and database drivers throw at runtime, not fail gracefully. And flush after your transaction commits, not before. Flush too early, and a request landing in that window reads the pre-update row, caches it, and now you're serving stale data for the full TTL with nothing telling you it happened.

Laravel 13 also adds Cache::touch(), which is worth knowing about for the more common case: a key is still valid, and you just want to push the TTL out without paying to rewrite the payload.

7. Model::all() works fine until your table grows

It's one call, it reads clean, and it's the answer in every tutorial. On a few hundred rows, it's genuinely correct. On a table with hundreds of thousands of open shipments, it's an OOM before your loop runs a single iteration — and the failure has nothing to do with your loop logic. It happens at the query level.

The choice comes down to two questions: does your loop mutate the column you're filtering on, and is each iteration doing slow I/O?

Read-only, fast → chunk()
Mutates the filtered column → chunkById() — pages by primary key instead of offset, so a row changing status mid-loop can't silently shift what the next page fetches (this is the one that bites people: chunk() under mutation doesn't error, it just quietly skips rows)
Slow outbound I/O → lazyById() — same generator ergonomics as cursor(), but doesn't hold a database connection open for the full duration of every outbound API call

phpShipment::where('status', 'in_transit')
->lazyById(500)
->each(function (Shipment $shipment): void {
$shipment->refreshTrackingStatus();
$shipment->save();
});

Get the second question wrong, and you trade an OOM for silently under-processing your table — which is arguably worse, because nothing throws to tell you it happened.

None of these is exotic. They're all things Laravel gives you out of the box. The theme across all seven, if there is one, is the same: local dev and a small seed dataset will hide every one of these problems from you, and production will find every single one at the worst possible time. Building the guardrail in — a DTO, a container binding, a lazy-loading exception, a version header — beats trying to remember not to make the mistake.

If you've hit a different version of any of these, I'd genuinely like to hear it — drop it in the comments.