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

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.

1. The outer scan on Assignment was filesorting on modified after filtering. The index in use didn't cover the sort column.
2. 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