78 seconds to 2: the covering index that fixed our slowest page

This is a submission for DEV's Summer Bug Smash: Smash Stories powered by Sentry.

The Problem

One of our reporting pages took 78 seconds to load for a large customer. Not "slow" — unusable. People assumed it was broken, because a page that takes 78 seconds may as well be.

The fix was a single database index. But the interesting part isn't the index; it's why four existing indexes on the same table didn't help, and how to ship an index against a live table without locking it.

What the page does

It's an assessment details page on a workplace learning platform. It answers a simple question: for one assessment, show me every learner, their current status, and when they first engaged with it.

The data model behind it is straightforward. Every time a learner attempts an assessment, we write a row. Learners retake assessments, so one learner can have many rows. Some rows get archived when a course is renewed; some get soft-deleted. So the page needs the latest live attempt per learner, which in SQL is the familiar group-and-aggregate shape:

SELECT user_id,
       MAX(id)         AS max_id,      -- latest attempt → current status
       MIN(created_at) AS started_at   -- first attempt  → engagement date
FROM user_assessments
WHERE lesson_content_assessment_id = ?
  AND moved_to_history = 0
  AND deleted_at IS NULL
GROUP BY user_id

One pass gives both the newest record and the oldest timestamp per learner. Nothing exotic.

For most customers this was fine. For one customer with roughly 20,000 attempts on a single assessment, it took 78 seconds.

Why the existing indexes didn't help

The table already had four indexes:

(course_lesson_id)
(lesson_content_assessment_id)
(lesson_content_assessment_id, is_submitted, total_marks)
(user_id)

At a glance that looks well covered. The query filters on lesson_content_assessment_id, and there's an index on exactly that column. So what's slow?

The index gets you to the right rows and then abandons you.

MySQL uses (lesson_content_assessment_id) to find the ~20,000 rows for that assessment. But the query has two more conditions — moved_to_history = 0 and deleted_at IS NULL — and neither column appears in any index. To evaluate them, the engine has to go and fetch each row from the table itself. That's 20,000 random lookups into the clustered index, one per candidate row.

Then, having assembled the surviving rows, it still needs GROUP BY user_id. The index it used is ordered by lesson_content_assessment_id, not by user_id, so the grouping can't ride on index order. MySQL builds a temporary table and sorts.

So the shape of the work was: narrow the range with an index, then do 20,000 random I/Os to apply filters the index couldn't answer, then materialise and sort a temp table to group. Each of those is individually reasonable. Together, on a table of ~700,000 rows, they're 78 seconds.

The third index — (lesson_content_assessment_id, is_submitted, total_marks) — is a good illustration of why "we have an index on that column" isn't a useful statement. It was built for a different query. Its second and third columns are irrelevant here, so it degrades to the same behaviour as the single-column one.

The fix

add_index :user_assessments,
          %i[lesson_content_assessment_id moved_to_history deleted_at user_id id created_at],
          name: 'idx_ua_lca_history_user',
          algorithm: :inplace,
          if_not_exists: true

Six columns, and the order is the whole point. Read it as three groups:

lesson_content_assessment_id, moved_to_history, deleted_at — the three equality filters, leading the index. Now the engine seeks directly to the block of rows matching all three conditions, rather than matching one and testing the rest row by row. The 20,000 random lookups disappear.

user_id — next, because the query groups by it. Within that filtered range, entries are already ordered by user_id, so grouping is a sequential walk. No temporary table, no filesort.

id, created_at — last, because they're the aggregated values. MAX(id) and MIN(created_at) can be read straight from the index entries.

That final group is what makes it a covering index: every column the query touches — filtered, grouped, or selected — lives in the index. The engine never reads the table at all. It answers the entire query from the index structure.

The same access pattern also serves the pagination COUNT(*), which had been paying the identical cost on every page load.

Result: 78 seconds to 1–2 seconds.

Shipping it without locking the table

Adding an index to a 700,000-row table that's being actively written to is where this could still have gone wrong.

By default, some index operations rebuild the table, holding a lock for the duration. On a live production table that's an outage. MySQL's online DDL avoids it:

ALTER TABLE user_assessments
ADD INDEX idx_ua_lca_history_user
(lesson_content_assessment_id, moved_to_history, deleted_at, user_id, id, created_at),
ALGORITHM=INPLACE, LOCK=NONE

ALGORITHM=INPLACE builds the index without copying the table. LOCK=NONE keeps concurrent reads and writes flowing throughout. Crucially, specifying them explicitly means the statement fails fast if the engine can't honour them, rather than silently falling back to a locking table copy. An error you can see beats an outage you can't explain.

I also deliberately did not let this run as part of a deploy migration. Two reasons: deploy migrations run at a time chosen by the release schedule, not by me, and a long-running DDL wedged into a deploy is a bad place to discover a problem. So the index ships as a rake task that can be run in a controlled window:

task add_detail_ua_index: :environment do
  next puts 'Index already exists — nothing to do.' if conn.index_name_exists?(table, index_name)
  conn.execute(sql)   # INPLACE / LOCK=NONE
end

It's idempotent, so re-running is harmless. The migration carries if_not_exists: true, so once the task has created the index the migration is a no-op wherever it runs afterwards — production, staging, or a fresh developer database. And there's a matching task to drop the index the same way, because a performance change you can't reverse is a performance change you should be nervous about.

What I took from it

"There's an index on that column" doesn't mean the query is indexed. An index helps only to the extent it covers what the query actually asks — filters, grouping, ordering, and selected columns. A partial match can leave you with the worst outcome: the optimiser confidently picks an index that gets you halfway and then does the expensive part row by row.

Column order encodes the query. Equality filters first, then grouping and ordering columns, then the values you need to read. That sequence isn't a convention; it's a description of how the engine will walk the structure.

The diagnosis is the work. Writing add_index took a minute. Understanding why 20,000 rows became 78 seconds — that the filters forced row lookups and the grouping forced a temp table — is what made it possible to choose the right six columns instead of guessing.

Plan the rollout, not just the change. Explicit ALGORITHM/LOCK, an idempotent task, a no-op migration, and a reverse task. The index took seconds to build. Deciding how to build it took longer, and that was time well spent.