Sample MySQL diagnostic: slow order history in a multi-tenant application

Illustrative example — not a customer engagement. The scenario is synthetic and does not contain production data or measured performance results. The schema, query and plan excerpt were written for this example. Nothing below was produced by running MySQL against real data, and no timings or row counts are quoted because none were measured.

This sample shows the structure and the level of detail of a DB-Ex MySQL-family performance diagnostic report. A real report follows the same sections, with the evidence that was actually reviewed.

1. Executive summary

The order-history page runs one query that filters by tenant and customer and sorts by creation time. No existing index serves both the filter and the sort, and deep pages use OFFSET pagination. Both are consistent with the report that the page is slow mainly for large tenants. They are not yet proof of the cause.

The evidence supplied does not include an execution plan for a slow, large tenant. The first recommendation is therefore to capture that evidence, which takes little effort, before changing anything. The most promising change is a composite index, but it has not been tested and should be validated on a representative copy of the data before it goes near production.

What the team should do next:

1. Capture execution plans and statement statistics for two large tenants (section 7, R1).
2. Test the candidate composite index on a staging copy with a realistic data distribution (R2).
3. Plan a move from OFFSET pagination to keyset pagination for deep pages (R3).

2. The question investigated and agreed scope

Principal question: why does the order-history page become slow for some tenants, and what should the team test first?

Agreed scope:

• One environment: production, reviewed through read-only evidence supplied by the team.
• One query family: the order-history listing and its pagination.
• Evidence supplied by the team; no direct access to the database was requested.
• A written report and a one-hour technical handover call.

3. Environment and assumptions

Synthetic schema, reduced to the relevant columns:

CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id    INT UNSIGNED    NOT NULL,
  customer_id  BIGINT UNSIGNED NOT NULL,
  status       VARCHAR(20)     NOT NULL,
  created_at   DATETIME(3)     NOT NULL,
  total_amount DECIMAL(12,2)   NOT NULL,
  PRIMARY KEY (id),
  KEY idx_tenant_created (tenant_id, created_at),
  KEY idx_customer (customer_id)
) ENGINE=InnoDB;

The query behind the page:

SELECT id, status, created_at, total_amount
FROM orders
WHERE tenant_id = ?
  AND customer_id = ?
  AND status IN ('paid', 'shipped', 'refunded')
ORDER BY created_at DESC
LIMIT 20 OFFSET ?;

4. Evidence reviewed

Synthetic plan excerpt for the small tenant (key columns only):

table: orders   type: ref   key: idx_customer
Extra: Using where; Using filesort

This plan was supplied for a small tenant only. The optimiser can choose a different plan for a large tenant, so it cannot be used to explain the slow cases.

5. Findings

Each finding is marked as an observation (directly supported by the evidence), a hypothesis (a plausible explanation that still needs testing) or an open question.

6. Missing evidence and remaining uncertainty

The review cannot establish the root cause with the evidence supplied. Specifically:

• Large-tenant plans. The main hypothesis (F4) depends on how the optimiser plans the query for a tenant with many orders. This is the most important missing item.
• Where the time goes. There are no statement statistics or wait data, so it is not known what share of the page’s response time is spent in MySQL (F6).
• Data distribution. The number of orders per tenant and per customer is described but not measured. Index effectiveness depends on it.
• Write load. An extra index adds work to every insert and to updates of indexed columns. The insert rate on orders is not known.

If the missing evidence shows that most of the time is spent outside query execution, recommendations R2 and R3 may not address the reported slowness, and the next step would be a different investigation.

7. Prioritised recommendations and prerequisites

Notes on R2:

• Putting status between customer_id and created_at is not recommended as a first test. With an IN list on status, MySQL would read several ranges and would usually need to sort again, which removes the benefit for ORDER BY created_at.
• Whether the optimiser chooses the new index for every tenant size has to be checked on the staging copy, not assumed.

Sketch of keyset pagination for R3, ordered by created_at then id so that rows with equal timestamps are paged deterministically:

SELECT id, status, created_at, total_amount
FROM orders
WHERE tenant_id = ?
  AND customer_id = ?
  AND status IN ('paid', 'shipped', 'refunded')
  AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 20;

The placeholders after the status filter are the created_at and id of the last row on the previous page.

8. Validation steps and change/rollback considerations

Validation for R2, on a staging copy first:

1. Record the baseline: EXPLAIN ANALYZE for a small and a large tenant, first page and a deep page, and the rows examined for the statement digest.
2. Add the index on staging and repeat the same measurements with the same parameters.
3. Accept the change only if the large-tenant plan uses the new index and examines fewer rows, and the small-tenant plan does not get worse.
4. Measure insert throughput on orders before and after, using the team’s own load test, to see the write cost of the extra index.

Change and rollback considerations:

• Adding a secondary index to an InnoDB table can normally run as online DDL, but it still takes short metadata locks at the start and end, which wait behind long-running transactions. Schedule it and check for long transactions first.
• Check the behaviour for replicas on the platform and version in use, and the extra storage the index needs.
• To roll back, the index can first be made invisible (ALTER TABLE orders ALTER INDEX idx_tenant_customer_created INVISIBLE, MySQL 8.0), which stops the optimiser from using it without dropping it, then dropped once the effect is confirmed.
• R3 changes the page contract. Deploy it behind a flag or for deep pages first, so that the old path remains available.

9. Handover notes and next decisions

For the implementation team:

• Decision 1: who can supply the R1 evidence, and from which replica or copy. This decides whether R2 goes ahead.
• Decision 2: whether the API can change to keyset pagination (R3), or whether deep pages can be limited instead.
• Decision 3: what response time the team considers acceptable for this page, so that the validation in section 8 has a target.
• The handover call walks through F1–F6, the reasoning behind R2, and the validation steps, and answers the implementation team’s questions.
• Recommendations are implemented by the team or its implementation partner. DB-Ex can review the R1 evidence under a follow-up scope if the team wants a second look.

10. Outside the scope of this investigation

• Implementing or deploying any of the recommendations, and any production change.
• Other queries, pages or tables beyond the order-history listing.
• Capacity sizing, instance class selection and cost optimisation.
• Continuous monitoring, on-call cover or emergency response.
• Any guarantee of a particular improvement. The expected effect of R2 and R3 has to be measured, not assumed.