Removing a repeated COUNT cut issue-list response time by 46%

TL;DR

The issue list was slow, and every “load more” was as slow as the first page. Most of that time went to one number, the total at the top of the list. To get it, the API counted every matching row on every request, also for page 2, 3 and 4. I moved the count to its own cached endpoint. In production, the median of the issue list went from 654 ms to 354 ms (−46%), the site list from 358 ms to 291 ms (−19%), and the p95 of the document list from 4431 ms to 2824 ms (−36%).

Context

The platform is a multi-tenant SaaS for housing operators. The API is Node.js on Strapi v4 with MySQL. The frontend shows most collections as lists with filters and infinite scrolling.

Strapi has a global default, withCount: true. With it, every paginated find runs two queries: one for the page, and one COUNT for the total. Nobody had questioned that default.

Symptoms

The issue and task lists felt slow, and every “load more” felt as slow as the first page. On a copy of one of the larger tenants, /api/issues had a p50 of 1060 ms.

Cause

By default, Strapi runs two queries for each list request. One query gets the page. The other query is a COUNT for the total. The page query stops after one page, but the COUNT cannot stop early. It goes through every matching issue, through the same joins that scope the list to a tenant (issue → unit → site → tenant).

In a local benchmark, the COUNT was 84% of the request time for /api/issues and 73% for /api/tasks.

Infinite scrolling made it worse. Each “load more” ran the full COUNT again, and each time it returned the same number.

Fix

No count by default. I turned off withCount globally. List responses now return { page, pageSize }. A client that needs the total can still ask for it with pagination[withCount]=true.

// config/api.js (simplified)
module.exports = {
  rest: {
    withCount: false,
  },
};

A separate count endpoint. I added GET /<entity>/count for 12 collections. It uses the same tenant policies as find, and it caches the result in an LRU cache (5000 entries, 30 min TTL). The cache key is the collection, the tenant filter and the final filters.

// simplified
const cache = new LRUCache({ max: 5000, ttl: 30 * 60 * 1000 });

async function count(ctx, uid) {
  const where = withTenantFilter(ctx, ctx.query.filters);
  const key = `${uid}:${JSON.stringify(where)}`;

  if (!cache.has(key)) {
    cache.set(key, await strapi.db.query(uid).count({ where }));
  }

  return { count: cache.get(key) };
}

Invalidation. A lifecycle hook clears all cached counts of a collection when a record of that collection is created, updated or deleted.

// simplified
strapi.db.lifecycles.subscribe({
  models: COUNTED_COLLECTIONS,
  afterCreate: (event) => clearCounts(event.model.uid),
  afterUpdate: (event) => clearCounts(event.model.uid),
  afterDelete: (event) => clearCounts(event.model.uid),
});

The frontend. The list calls /count once when the filters change, in parallel with page 1, through one shared useCount composable for all pages. Infinite loading stops when a page comes back shorter than the page size, so it no longer needs pageCount.

// simplified
const [firstPage, total] = await Promise.all([
  fetchIssues({ filters, page: 1 }),
  fetchIssueCount({ filters }),
]);

// on every "load more"
const hasMore = nextPage.items.length === pageSize;

One side effect I found before the release: withCount is also the default for internal service.find() calls, not only for REST responses. A dashboard endpoint read pagination.total from such a call and would have received undefined. It now uses the same cached count.

How I measured

Local. A copy of one of the larger tenants, 30 requests per endpoint, p50.

Production. In Grafana, I compared the days after the release with the same weekdays one week before, from Friday to Monday. For the document list I used six days, because it gets fewer requests. I looked at the median and the p95, the response-time threshold at or below which 95% of requests fall.

These are API response times, not browser page-load times. The comparison is observational: this write-up does not include request counts, exported charts or controls for changes in traffic mix. The local benchmark isolates the change more directly, but its gains should not be substituted for the production results.

Results

Local benchmark, p50:

Production:

List requests now run zero COUNT queries. Small collections showed almost no change locally, so the gain grows with the size of the collection.

Trade-offs

• A total can be up to 30 minutes old when a filter uses a related collection, or when another API instance made the change. That was acceptable for a number that only informs.
• On purpose, /count uses the tenant policies but not every permission check of find. A user with partial access can see a total that is higher than the list.
• External API-token clients no longer get meta.pagination.total unless they ask for it.
• The frontend had to ship together with or before the API, and the roles needed the new count permission. Without it, the count endpoints returned 403.