Drupal slow queries — identifying the offending view or entity query

1. Problem

A category page that used to render in 400ms now takes seven, eight, sometimes twelve seconds. Admin overview tables crawl. A specific node listing hangs the editor UI. PHP-FPM workers stay tied up long enough that other requests queue. CPU sits at 80%, MariaDB shows a flat wall of Sending data states, and Drupal's status report cheerfully reports no errors.

You searched "drupal find slow query view" because the symptom is clearly a database problem, but Drupal will not tell you which view, entity query, or contrib module fired the query. Devel's query log dumps everything from a single request — finding the offending query inside dozens of fast ones is itself the work. You need to identify the specific view or entity query producing the slow SQL, in production, without a profiler that doubles your response time.

The signal that names the offending query exists. MySQL's slow log captures it. Drupal's hook timing reveals which view executed it. The page that triggered it is in the access log with a matching timestamp. The rest of this guide is how to correlate those three sources, narrow it to one view or EntityQuery, and fix the cause.

2. Impact

A slow query is not a styling bug. Each request that hits the slow path holds a PHP-FPM worker hostage for the duration of the query. PHP-FPM has a finite worker pool. Once those workers are saturated, every other request — fast or slow — queues behind them.

Concrete impact:

• Cascading queue failure. A view that takes 6 seconds for one user becomes catastrophic at 50 concurrent visitors. Workers exhaust, new connections get 502s, and a single bad query takes the whole site down.
• Editor productivity collapse. Slow admin views mean editors wait 10 seconds per click. Content updates stall and editors blame "Drupal" generically — so it never gets reported as a specific bug.
• Database lock contention. Long SELECT queries holding row-level read locks block writes from cron, queue workers, or editors.
• Replica lag. Slow queries on the primary translate into replica lag — logged-in users see stale content.
• Cost. Many sites respond to "everything is slow" by upsizing the database. The actual fix is usually a missing index that costs nothing.

The expensive part is the gap between "the site got slow" and "this view produces the bad query." Most teams close that gap by guessing. This guide closes it with evidence.

3. Why It’s Hard to Spot

Drupal is structurally bad at attributing slow database work back to the module that caused it.

• The status report stays green. It checks configuration, cron, file permissions. It does not measure query latency. Every page can take 8 seconds and the status report shows no warnings.
• Devel's query log is per-request and overwhelming. It dumps every query from one page load — typically 80–300 — to the screen. Spotting the slow one is its own task, only visible on the request you actively reload.
• Generic APM sees the request, not the view. New Relic tells you /products/category/widgets is slow. It cannot tell you the cause is view.products_listing.page_1 or an EntityQuery in a custom block.
• Watchdog does not log slow queries. It records PHP errors and module log writes. Query latency is not surfaced there.
• Plans change under load. A query that is fast in dev with 1k rows becomes slow in production with 1M rows because MySQL picks a different join order. You cannot reproduce the slow plan locally.
• Caching masks it until invalidation. Drupal's render and Views result caches hide the slow query for warm requests. The slow path only fires on cache miss — typically after a deploy, content edits, or for logged-in users.

The result: a slow Drupal view is invisible to the tools Drupal ships with. It is loud to visitors and silent to your monitoring.

4. Cause

A Drupal page renders by stitching together the output of many small components — blocks, views, entity displays, menus — each issuing SQL through Drupal's database layer. When one issues a query exceeding MySQL's long_query_time threshold (default 10s; typically lowered to 1s or 2s for diagnostic work), MariaDB writes an entry to the slow query log. In Logystera terms, that entry is a db.slow_query signal.

A db.slow_query signal carries the full SQL text, query time, lock time, rows examined, rows sent, the user/host that issued it, and a microsecond-accurate timestamp.

The most common Drupal sources of db.slow_query:

• Views with unindexed filter columns. A View filtering on a custom field column with no index forces a full table scan. At 100k nodes that is a 3–6 second scan every page load.
• EntityQuery with condition() on base fields that join multiple tables. Drupal's EntityQuery generates 7-way joins across node, node_field_data, node__field_*. One missing index on a join column kills it.
• Views with relationships and aggregation. COUNT(DISTINCT) over relationships generates SQL the optimizer cannot index.
• Pager queries without total count optimization. The default Views pager runs the query twice — once for the page, once with COUNT(*). The count query is often the slow one.
• Revision table scans. Queries that forget default_revision = 1 scan the whole history of revision-heavy entities.

The signal does not say "Drupal is slow." It names a specific SQL statement that took too long, and the SQL text fingerprints which view or EntityQuery produced it. That fingerprinting is the diagnostic core of this guide.

5. Solution

5.1 Diagnose (logs first)

This is the part that matters. Every step ties back to the db.slow_query signal, because the SQL text is the only piece of evidence that names the failing query.

1. Turn on the MySQL slow query log

If it is not already on:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

For persistent config, set the same keys in my.cnf under [mysqld] and reload MariaDB. Every query slower than 1 second now writes a db.slow_query entry.

2. Find slow queries that look like Views or EntityQuery

Drupal's database layer prefixes queries with a comment that identifies the source. You can grep for view-shaped and entity-shaped patterns directly:

# Slow queries from Views (Views always SELECT FROM {node_field_data} or similar with aggregation)
grep -B1 -A30 "SELECT.*FROM.*node_field_data.*GROUP BY" /var/log/mysql/slow.log | less

# Slow queries that hit field tables (typical EntityQuery shape)
grep -E "node__field_|node_revision__field_" /var/log/mysql/slow.log | head -50

# Top 10 slowest queries by query time
grep -E "Query_time" /var/log/mysql/slow.log | sort -k3 -rn | head -10

Each match is a db.slow_query signal. The SQL text is the fingerprint.

3. Use mysqldumpslow or pt-query-digest to aggregate

A single slow log can have thousands of entries. Aggregate them by query pattern:

# Top 20 slowest query patterns
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# Or with Percona toolkit (more useful):
pt-query-digest /var/log/mysql/slow.log | head -200

pt-query-digest groups queries by structural fingerprint, ranks them by total time consumed, and shows the worst-case example. The top entries are your offenders. Each represents a recurring db.slow_query signal.

4. Correlate slow queries to URLs via the access log

You have the SQL and the timestamp. To attribute it to a Drupal route, match the slow query's timestamp to the web server access log:

# Slow query at 14:23:07.842
grep "14:23:0[6-8]" /var/log/nginx/access.log

The matching URL — say /admin/content or /products/widgets — tells you which page triggered the query. Now you know the query, the page, and the timestamp. That triangulation is what db.slow_query correlated with http.request gives you.

5. Identify whether it is a view or an EntityQuery

The SQL shape distinguishes them:

• Views queries typically SELECT node_field_data.nid AS nid ... FROM {node_field_data} with multiple INNER JOIN on node__field_ and a separate COUNT() companion.
• EntityQuery queries use a base table alias of base_table (literally — Drupal generates FROM {node_field_data} base_table) with LEFT OUTER JOIN for revision and field joins.
• Custom Drupal::database()->select() queries use whatever alias the developer chose.

Add a query comment in custom code so future slow logs self-identify:

$query = \Drupal::database()->select('node_field_data', 'n')
  ->comment('mymodule:product_listing_block');

The comment / mymodule:product_listing_block / appears verbatim in the slow log.

6. Find the view by name

If the slow SQL looks like a Views query, export every view's generated SQL via Drush and grep:

drush ev "foreach (\Drupal\views\Entity\View::loadMultiple() as \$id => \$v) { foreach (\$v->get('display') as \$d_id => \$d) { \$exec = \Drupal::service('views.executable')->get(\$v); \$exec->setDisplay(\$d_id); \$exec->build(); echo \"=== \$id:\$d_id ===\n\" . \$exec->build_info['query'] . \"\n\n\"; } }" > /tmp/views-sql.txt

grep -B2 "field_product_category_target_id" /tmp/views-sql.txt

The matching view ID is the offender.

7. EXPLAIN the query

Once you have the slow SQL, run EXPLAIN against it to confirm the cause:

EXPLAIN SELECT node_field_data.nid AS nid, ...
FROM node_field_data
INNER JOIN node__field_product_category ON ...
WHERE field_product_category_target_id = 42;

Look for type: ALL (full table scan), rows: showing hundreds of thousands, or Extra: Using temporary; Using filesort. Each is a known cause of db.slow_query.

5.2 Root Causes

(see root causes inline in 5.3 Fix)

5.3 Fix

Fixes map directly to which root cause produced the db.slow_query signal. Address them in this order — most common first.

Cause: Missing index on a field column

Signal shape: db.slow_query with EXPLAIN showing type: ALL on a node__field_* table.

Drupal does not auto-index custom field columns beyond the entity ID. Add an index via hook_update_N:

function mymodule_update_9001() {
  $schema = \Drupal::database()->schema();
  $schema->addIndex(
    'node__field_product_category',
    'field_product_category_target_id_idx',
    ['field_product_category_target_id'],
    ['fields' => ['field_product_category_target_id' => ['type' => 'int']]]
  );
}

Re-run the slow query. Query time should drop from seconds to milliseconds. The db.slow_query signal stops firing for that pattern.

Cause: Views pager count query

Signal shape: Two db.slow_query entries fire per page load — the page query and a SELECT COUNT(*) companion.

In the View edit UI, switch the pager from "Paged output, full pager" to "Paged output, mini pager" (skips the count query). For complex views, override the count via hook_views_query_alter():

function mymodule_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() === 'products_listing') {
    $query->total_rows = 50000; // approximate, avoids COUNT(*)
  }
}

Cause: EntityQuery with too many conditions on field tables

Signal shape: db.slow_query with 5+ LEFT JOIN on node__field_* tables.

Each condition() on a field adds a join. Replace with a direct database query when you have more than three field conditions, or denormalize the filter columns into a custom table updated via entity hooks. The db.slow_query signal disappears once joins drop below three.

Cause: Revision table scan

Signal shape: db.slow_query against _revision or _field_revision tables, rows examined in the millions.

Always include default_revision = 1 in queries against revision tables. In EntityQuery, this is implicit — verify it has not been disabled with allRevisions(). Audit custom queries for missing revision filters.

Cause: Render-time queries inside loops

Signal shape: Many small db.slow_query entries clustered within a 200ms window from the same connection, often correlated with a perf.hook_timing spike on hook_views_pre_execute.

A custom field formatter or preprocess function is querying inside a loop over rendered rows. Move the query to hook_preprocess_views_view() or similar and load all entities in one loadMultiple() call. The clustered db.slow_query pattern collapses into a single query.

Cause: Stale table statistics

Signal shape: A previously-fast query suddenly produces db.slow_query with no schema change.

The optimizer picked a bad plan because stats are stale. Run ANALYZE TABLE node_field_data, node__field_product_category;. If it recurs, increase innodb_stats_persistent_sample_pages.

5.4 Verify

The signal that should stop appearing is db.slow_query for the specific SQL pattern you fixed.

After deploying the index, the count query change, or the query refactor:

# Confirm no new entries for the fixed pattern in the last 30 minutes
awk -v cutoff="$(date -d '30 minutes ago' '+%Y-%m-%dT%H:%M')" \
  '$0 ~ /^# Time:/ && $3 > cutoff {flag=1} flag && /field_product_category/' \
  /var/log/mysql/slow.log

# Check overall slow query rate
mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"

Healthy state:

• The fixed query pattern produces zero new db.slow_query entries for 30 minutes under representative traffic.
• Slow_queries counter increments drop to near zero (a few per hour is acceptable; hundreds per minute is not).
• The triggering URL's response time returns to its pre-incident baseline.
• perf.hook_timing for hook_views_pre_execute on the affected view drops below 100ms.

If db.slow_query keeps firing for the same pattern, the index was not applied (SHOW INDEX FROM table_name), the view cache was not cleared (drush cr), or the optimizer is still using the old plan (ANALYZE TABLE).

6. How to Catch This Early

Fixing it is straightforward once you know the cause. The hard part is knowing it happened at all.

This issue surfaces as db.slow_query.

The hard part of slow Drupal queries is not fixing them. It is knowing they happened before users complain. By default:

• MySQL writes db.slow_query to a log file nobody reads.
• The slow log rotates and gets truncated before anyone correlates it to a Drupal route.
• Watchdog stays silent. The status report stays green.
• The first signal anything is wrong is a support ticket or a bounce-rate spike.

This class of failure surfaces as the db.slow_query signal, supplemented by perf.hook_timing on hook_views_pre_execute and correlated http.request latency. Logystera ingests those signals, attributes them to the Drupal entity that issued the query, and alerts when slow query rate or p95 query time crosses threshold — the moment the slow path activates, not when an editor finally complains.

Once db.slow_query is a first-class signal instead of log noise, "find the offending view" becomes a triage step that takes minutes, not a half-day archaeological dig.

7. Related Silent Failures

Other Drupal failure modes that share signal proximity with db.slow_query:

• Cron timeouts. Long-running update hooks or queue workers exceeding max_execution_time produce a mix of db.slow_query and php.fatal ("Maximum execution time exceeded") signals.
• Memory exhaustion on entity loads. Loading thousands of entities in one request triggers memory_near_limit then php.fatal, often correlated with the same view that produces slow queries.
• Missing field index after a hook_update_N. A field added without its index produces db.slow_query immediately at the next page render.
• Search API index drift. When the Search API index falls out of sync, fallback queries hit the database directly and produce db.slow_query patterns identical to unindexed Views queries.
• Replica lag from long SELECTs. Sustained db.slow_query on the primary causes replica lag, surfacing as watchdog warnings about stale render cache.

Each is a different signal pointing at the same truth: Drupal's database layer has no built-in way to tell you when it is in trouble. The logs do.