Acquia Back End Specialist (D10): A view that times out only for authenticated editors

A view that times out only for authenticated editors

An editorial dashboard view lists 200,000 nodes with three exposed filters, two relationship joins to a taxonomy term reference, and a computed "days since last update" field implemented in a hook_views_query_alter(). Anonymous visitors never see it. Editors with the editor role report the page taking 30+ seconds and sometimes returning a 504. The database is MySQL 8 on an Acquia Cloud Enterprise environment; the view's query has no indexes on the changed column it sorts by. Caching is configured with tag-based cache and a 1-hour max-age. A developer proposes adding #cache['max-age'] = 3600 and calling it done.

1. Explain why the caching proposal will not solve the problem for editors, and what the actual bottleneck is.

Read the worked answer

The cache proposal fails for two reasons.

It doesn't help the first request. Tag-based caching with a 1-hour max-age means the first editor to load the dashboard after any invalidation still pays the full 30-second query. If editors hit it throughout the day and any node save invalidates the tag, the cache is cold most of the time. A 504 on a cold cache is exactly the symptom reported.

It may not cache at all. Views with exposed filters and per-user access produce cache variations; if the view's cache plugin isn't configured to vary by the exposed input and by user permissions, Drupal either refuses to serve the cached result to a different editor or serves the wrong one. Setting max-age on the render array doesn't fix a query that isn't cacheable in the first place.

The real bottleneck is the query: a sort on an unindexed changed column over 200,000 rows, plus two relationship joins that multiply rows, plus a computed field evaluated in PHP via hook_views_query_alter(). The database is doing a filesort over the full set before applying the LIMIT. Caching hides this on a warm cache and does nothing on a cold one.

2. Give a concrete remediation plan, ordered by impact, that addresses the query rather than the symptoms.

Read the worked answer

Index changed. Add a database index on node_field_data.changed (or the relevant field table). This alone can turn a filesort into an index scan and is the highest-leverage change. On Acquia Cloud, ship it as a hook_update_N() using the schema API so it deploys with the code.
Move the computed field into SQL. hook_views_query_alter() computing "days since update" in PHP forces the query to fetch all rows before filtering. Express it as a SQL expression (DATEDIFF(NOW(), changed)) added via the query alter, so the database can filter and sort on it.
Reduce the row set before the joins. Add a mandatory base filter (e.g. published, type = article) and check whether the taxonomy relationships are INNER JOINs that multiply rows. If they are only needed for display, use a views_join with the right cardinality or drop them from the query and load terms in the renderer.
Paginate aggressively. A dashboard does not need 200,000 rows; set the pager to 25 and ensure the query has a LIMIT before the joins are applied.
Only then tune caching, with correct cache contexts for the exposed filters and a shorter max-age, as a safety net rather than the fix.

3. How would you confirm the fix in production-like conditions without a maintenance window?

Read the worked answer

Reproduce the query, don't guess. Enable the database query log on a non-production Acquia environment (or use EXPLAIN on the exact SQL the view generates, captured from the views query log or devel). Confirm the plan shows a filesort or full scan before the change and an index scan after.

Then measure the end-to-end path: use the Acquia Cloud "workflow" to deploy the code (including the hook_update_N) to a staging environment that mirrors production data volume, run drush updatedb, and time the dashboard as an editor role. Check SHOW INDEX to confirm the index exists and EXPLAIN to confirm it's used. Finally, watch the production 504 rate and slow-query log for a day after deploy rather than declaring victory from a single fast load - the failure mode was cold-cache, so the test must include a cache clear followed by an immediate request.