How I Took an ERP Report from 5 Seconds to 90 Milliseconds

The CFO at a client used to time the monthly P&L report on a stopwatch. Five and a half seconds. Three weeks later we took it to 90 milliseconds — same query, same data, no new hardware. Here is the actual sequence, in order.

The starting point

The query joined six tables: ledger, accounts, cost_centres, budget_lines, departments, fy_periods. The ledger table had 47 million rows. The query took 5.4s on average, occasionally 8s. The ops team had taken to running it before lunch so it would be ready when they came back.

Step 1 — Read the EXPLAIN, do not guess

The number-one mistake I see is people adding indexes before reading the execution plan. Always EXPLAIN first.

EXPLAIN ANALYZE
SELECT a.name, SUM(l.amount) AS total
FROM ledger l
JOIN accounts a ON a.id = l.account_id
WHERE l.posted_on BETWEEN '2026-04-01' AND '2026-04-30'
  AND l.company_id = 42
GROUP BY a.name
ORDER BY total DESC;

The plan revealed exactly what was wrong:

• A full table scan on ledger (47M rows).
• Using temporary; Using filesort for the GROUP BY.
• The composite index on (company_id, posted_on) existed but was not being used because the planner chose a different one.

Step 2 — Fix the index, not just add one

There was already a (posted_on) index. The planner liked it. The problem: with 47M rows over five years, even a date range of one month still pulls back hundreds of thousands of rows before filtering by company. We dropped that index and added a composite index whose first column is the most selective:

DROP INDEX idx_ledger_posted ON ledger;
CREATE INDEX idx_ledger_company_posted
  ON ledger (company_id, posted_on, account_id);

Re-ran EXPLAIN. The plan now used idx_ledger_company_posted and the row estimate dropped from 47M to ~120k. Query dropped to 1.8s. Worthwhile, not done.

Step 3 — Avoid the temporary table

The GROUP BY a.name still required a temporary table because a.name came from the joined table. We rewrote to GROUP BY the foreign key first, then join only the survivors:

SELECT a.name, totals.total
FROM (
    SELECT l.account_id, SUM(l.amount) AS total
    FROM ledger l
    WHERE l.company_id = 42
      AND l.posted_on BETWEEN '2026-04-01' AND '2026-04-30'
    GROUP BY l.account_id
) totals
JOIN accounts a ON a.id = totals.account_id
ORDER BY totals.total DESC;

The inner query is now a pure index scan + aggregation on a small slice. Query dropped to 410ms.

Step 4 — Materialise the part that does not change

For a P&L report, the totals for past months are immutable. There is no reason to recompute April after May has begun. We added a small materialised summary table that gets updated nightly:

CREATE TABLE ledger_month_summary (
  company_id INT NOT NULL,
  posted_month CHAR(7) NOT NULL,   -- '2026-04'
  account_id INT NOT NULL,
  total DECIMAL(18,2) NOT NULL,
  PRIMARY KEY (company_id, posted_month, account_id),
  INDEX idx_company_month (company_id, posted_month)
) ENGINE=InnoDB;

The report now reads from this small table for past months and only falls back to the big ledger for the current (in-progress) month. Average query time landed at 90ms — about a 60x improvement.

Step 5 — Cache, carefully

We added a Redis cache layer with a 5-minute TTL on the rendered report data. Cache key includes company ID, month range, filters, and user permissions. Cache hits are now ~3ms. Cache misses still hit the optimised query, so even the slow path stays fast.

The numbers

What I refuse to do

• Add an index without reading EXPLAIN first. Indexes are not free — they slow writes and bloat the buffer pool.
• Cache as the first step. If the underlying query is wrong, you are caching a slow answer.
• "De-normalise" without a written reason. Once you do, every write path needs to update both copies, forever.

If your reports are slow

The hardest part of this work is not the SQL. It is being patient enough to read the plan, fix one thing, measure, then fix the next. Most "performance problems" I am called in for are three small wrong decisions stacked on each other, not one heroic fix. If yours is the same, see the contact section on the homepage.