Improve a dashboard's fetch API from 4+ Seconds to ~200 ms

Problem

A client’s application dashboard displayed a paginated list of events alongside related workflow information. The page returned only a small number of records, pagination was already in place, and DB had indexes. Yet, users still waited more than three seconds before the dashboard data table loaded.

The list endpoint was doing far more than loading the main critical data. Before returning anything, it calculated the count (for pagination) and fetched next action/step data which the table did not need for its first render.

The issue was easy to miss during development: small local datasets felt fast, and also not that prominent on staging. Production data volume exposed the true cost of the request path.

Constraints

• Preserve the dashboard’s existing behavior and keep detailed workflow information available.
• Improve the initial table load without a data model rewrite.
• Keep pagination useful before the exact result count is known.
• Support the common filters efficiently.

Diagnosis

Added observability for each database operation independently to understand what was causing the delay. The timings showed that pagination and the primary query were not the real bottlenecks:

• The primary records query was fast.
• The filtered COUNT(*) query took more than one second in production.
• The slowest next action related query took more than three seconds.
• The endpoint fetched and serialized more relationship data than the table needed to render.
• The API waited for every operation to finish before sending any data to the UI.

Some relationships were stored in a JSON aggregation structure, so resolving them required a JSON search across the related data table. Pagination limited the number of records returned, but it did not eliminate the exact count or the relationship work from the synchronous request.

The real bottleneck was the response boundary: critical and non-critical work shared the same path.

Solution

Split the workload into a critical request for the first render and a secondary request for supporting data (both started in parallel).

The critical API now returns only what the table needs immediately:

• Core event records.
• Minimal metadata regarding the next action.
• The identifier needed to retrieve the full relationship/data later in the next step.

Instead of waiting for an exact count, it requests limit + 1 records. The API returns the requested limit and uses the extra record as a lookahead. If that record exists, hasNextPage is true, allowing the UI to show provisional pagination such as 1 2 ….

A secondary request fetches the exact count and non-critical next step/action related data. It starts alongside the critical request, but the first render no longer depends on it. Once it completes, the UI replaces the provisional controls with the exact page count and fills in the supporting workflow information.

Record specific fields are deferred until a user opens an individual record. The existing JSON relationship lookup remains available for secondary and detail requests, but it no longer runs in the critical list-loading path. I also added a composite index for the common tenant-and-time filter pattern.

Trade-off

The frontend now coordinates two requests and briefly displays provisional pagination. The expensive JSON relationship lookup still exists (this is an optimization for another time); it has been moved away from the work users must wait for.

That was an intentional current stage compromise. In this case, changing when the work happened mattered more than further tuning the already-fast page query.

Stack

• Node.js
• Next.js
• MySQL

Outcome

• Cut the critical API response time from 4+ seconds to approximately 200 ms.
• Removed a 3+ second relationship lookup and a 1+ second count query from the initial response path.
• Let users begin working from the first page while exact pagination and supporting data loaded in the background.
• Reduced the amount of data fetched and serialized for the initial table render.
• Preserved all related data without making every list request pay its full retrieval cost.