Four indexes, none of them covering: the 78-second page and the one index that fixed it

A reporting page on a workplace learning platform took 78 seconds to load for one large customer, and the eventual fix was a single six-column index ^[1][2]. The useful part is not the index: the table already carried four indexes, including one on exactly the column the query filtered on, and none of them helped ^[3]. The account comes from a write-up by Nitish Anand on dev.to, submitted to DEV's Summer Bug Smash ^[1][20].

The page answers one question: for a given assessment, list every learner, their current status, and when they first engaged ^[4]. Each attempt is a row, learners retake, some rows are archived when a course is renewed and some are soft-deleted ^[5]. So the query is the standard group-and-aggregate shape: filter on `lesson_content_assessment_id`, `moved_to_history = 0` and `deleted_at IS NULL`, then `GROUP BY user_id` with `MAX(id)` and `MIN(created_at)` ^[6]. Fine for most tenants; one customer had roughly 20,000 attempts on a single assessment ^[7].

The four existing indexes were `(course_lesson_id)`, `(lesson_content_assessment_id)`, `(lesson_content_assessment_id, is_submitted, total_marks)` and `(user_id)` ^[3]. MySQL picked the second one, found the ~20,000 candidate rows, and then had nowhere to go: neither `moved_to_history` nor `deleted_at` appears in any index, so each candidate row had to be fetched from the clustered index to test the remaining filters ^[8]. That is 20,000 random lookups. Then, because the index is ordered by assessment id rather than `user_id`, the grouping could not ride on index order, so the engine materialised a temporary table and sorted it ^[9]. On a table of about 700,000 rows, those three steps together came to 78 seconds ^[10]. The candidate set was under 3 percent of the table ^[19].

The third index is the instructive one. It leads with the right column but its second and third columns are irrelevant to this query, so it collapses to the same behaviour as the single-column index ^[11]. "We have an index on that column" is not a statement about performance.

The replacement was `(lesson_content_assessment_id, moved_to_history, deleted_at, user_id, id, created_at)`, named `idx_ua_lca_history_user` ^[2]. Order is the whole design: three equality filters first so the engine seeks straight to the qualifying block, `user_id` next so grouping is a sequential walk with no temp table or filesort, then `id` and `created_at` last because the aggregates can be read out of the index entries ^[12]. Every column the query touches lives in the index, so the table is never read ^[13]. The pagination `COUNT(*)`, which had been paying the same cost on every page load, rides the same access path ^[14]. Reported result: 78 seconds down to 1 to 2 seconds ^[15], a 39x to 78x reduction ^[18].

The shipping risk is the part worth copying. Adding an index to a 700,000-row table under active writes is where this could still have gone wrong, because by default some index operations rebuild the table ^[16]; the migration therefore pins `algorithm: :inplace` and `if_not_exists: true` ^[17]. The published account breaks off mid-sentence at that point, so the operational detail of how the build behaved under load is not in the material ^[21].

Watch whether the covering index holds as the query grows. Add one column to the SELECT list that is not in the index and the covering property is gone, and you are back to row lookups. The same applies to any new filter on a column that sits after `user_id` in the key.