Last updated: 17 May 2026
Slow database queries are identified using slow query logs (MySQL: slow_query_log, PostgreSQL: log_min_duration_statement), EXPLAIN/EXPLAIN ANALYZE output and APM tools. The three most common causes are missing indexes, N+1 query patterns and SELECT * usage on large tables. Fixing the top 3 slow queries typically reduces database load by 40-70%.
Slow query logging is the first step in any database performance investigation. It creates a record of every query that exceeds a configurable time threshold, providing production-accurate data about which queries are actually slow -- not which queries look expensive in a code review or perform slowly in isolation on a developer machine with a tiny dataset.
MySQL slow query log:
# Enable in my.cnf for persistent configuration (requires MySQL restart)
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0 # Log queries taking > 1 second
log_queries_not_using_indexes = ON # Also log full table scans under 1 second
min_examined_row_limit = 1000 # Skip small table queries to reduce noise
# Enable at runtime without restart (settings lost on restart)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1.0;
SET GLOBAL log_queries_not_using_indexes = ON;
# Verify current settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
# Check how many slow queries have been logged in this session
SHOW STATUS LIKE 'Slow_queries';
PostgreSQL slow query logging:
# In postgresql.conf
log_min_duration_statement = 1000 # Log queries taking > 1000ms (1 second)
# Log all queries (WARNING: very high volume on busy servers)
# log_min_duration_statement = 0
# Log only queries that used temporary files (disk sort/hash operations)
log_temp_files = 0 # Log any temp file use
# log_temp_files = 10240 # Only log temp files > 10MB
# Enable pg_stat_statements for aggregate statistics (more useful than log files)
# In postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off # Skip COPY, VACUUM, etc.
# Apply without full restart (log settings only)
SELECT pg_reload_conf();
# Enable extension in each database that needs it
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
# View top slow queries by total execution time
SELECT
SUBSTRING(query, 1, 100) AS query_preview,
calls,
ROUND(total_exec_time::numeric / 1000, 2) AS total_seconds,
ROUND((total_exec_time / calls)::numeric, 2) AS avg_ms,
rows,
ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1)
AS cache_hit_pct
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;
Analysing MySQL slow query log with pt-query-digest:
# Install Percona Toolkit
sudo apt-get install percona-toolkit # Ubuntu/Debian
# Basic analysis -- rank by total execution time
pt-query-digest /var/log/mysql/slow.log
# Send report to a file for review
pt-query-digest /var/log/mysql/slow.log > slow_query_report.txt
# Focus on the last hour only
pt-query-digest --since 3600 /var/log/mysql/slow.log
# Sample pt-query-digest output for a problem query:
# Query 1: 847 QPS, 2897x concurrency, ID 0x9A...
# This item is included in the report because it matches --limit.
# Scores: Err/r 0, Distill Score 5218
# pct total min max avg 95% stddev median
# Count 45 847
# Exec time 62% 2897s 3s 5s 3.42s 4.81s 452ms 3.22s
# Lock time 0% 0 0us 2ms 0us 0us 24us 0us
# Rows sent 0% 50K 60 60 60 60 0 60
# Rows examine 5% 4.2M 5K 5K 5K 5K 0 5K
# Rows examine / rows sent: 5000 / 60 = 83 rows examined per row returned
# This ratio > 10:1 strongly indicates a missing index
Once slow query logging identifies the problem queries, EXPLAIN shows why they are slow. EXPLAIN instructs the database to show the execution plan it chose for the query -- which indexes it used (or did not use), in what order it joined tables, and how many rows it estimated to process at each step.
MySQL EXPLAIN key metrics:
EXPLAIN SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01'
AND o.status = 'pending';
-- Example output:
-- +------+-------+--------+------+-----------+-------+------+----------+-------+-------------+
-- | id | table | type | key | key_len | ref | rows | filtered | Extra |
-- +------+-------+--------+------+-----------+-------+------+----------+-------+-------------+
-- | 1 | o | ALL | NULL | NULL | NULL | 850K | 0.10 | Using where |
-- | 1 | c | eq_ref | PRIMARY | 4 | o.customer_id | 1 | 100.00 | NULL |
-- +------+-------+--------+------+-----------+-------+------+----------+-------+-------------+
-- Reading this output:
-- Row 1 (orders table):
-- type = ALL: FULL TABLE SCAN -- 850,000 rows scanned
-- key = NULL: no index used
-- rows = 850K: estimated 850,000 rows examined
-- filtered = 0.10: only 0.1% of rows match (850 rows returned from 850,000 scanned)
-- This is the problem: scanning 850K rows to return 850
-- Row 2 (customers table):
-- type = eq_ref: PRIMARY KEY lookup -- 1 row per lookup
-- key = PRIMARY: using the primary key index
-- This is fine -- efficient lookup
-- Fix: add composite index on the two filtered columns
CREATE INDEX idx_orders_status_date ON orders (status, order_date);
-- EXPLAIN after adding the index:
-- | 1 | o | range | idx_orders_status_date | 8 | NULL | 850 | 100.00 | Using index condition |
-- type = range: index range scan (good)
-- rows = 850: now scanning only the 850 rows that match (vs 850,000)
MySQL EXPLAIN type values from best to worst:
| type | Meaning | Performance | When You See It |
|---|---|---|---|
| system | Single row in system table | Best | Constant tables |
| const | Primary key or unique index lookup returning 1 row | Excellent | WHERE id = 123 |
| eq_ref | One row per row from previous table (JOIN using unique key) | Excellent | JOIN on PRIMARY KEY |
| ref | Index lookup returning multiple rows | Good | Non-unique index match |
| range | Index range scan | Good | BETWEEN, >, <, IN |
| index | Full index scan (reads entire index) | Acceptable for small tables | COUNT(*), covering index scan |
| ALL | Full table scan | Problem on large tables | Missing index on filtered column |
PostgreSQL EXPLAIN ANALYZE key metrics:
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01'
AND o.status = 'pending';
-- Key things to identify in output:
-- 1. Seq Scan on large table -- the primary red flag
-- Seq Scan on orders (cost=0..25000 rows=850000 width=32)
-- (actual time=0.023..3421.234 rows=850000 loops=1)
-- Filter: ((order_date >= '2026-01-01') AND (status = 'pending'))
-- Rows Removed by Filter: 849150
-- This shows: 850,000 rows scanned, 849,150 discarded -- missing index
-- 2. Good execution plan after adding the index:
-- Bitmap Heap Scan on orders (cost=45..2100 rows=850 width=32)
-- (actual time=2.345..8.234 rows=850 loops=1)
-- Recheck Cond: ((status = 'pending') AND (order_date >= '2026-01-01'))
-- -> Bitmap Index Scan on idx_orders_status_date
-- (actual time=1.234..1.234 rows=850 loops=1)
-- Index Cond: ((status = 'pending') AND (order_date >= '2026-01-01'))
-- Now scanning only 850 rows -- 1000x improvement
-- 3. Buffers: shared hit=N read=N
-- shared hit = pages served from shared_buffers (memory)
-- shared read = pages fetched from disk
-- High read counts on a frequent query = buffer pool undersized or missing index
The N+1 query problem occurs when application code executes one query to retrieve N parent records, then executes N additional queries -- one for each parent record -- to fetch related data. The result is N+1 total queries instead of 1 or 2. At small data volumes in development (10-20 records), N+1 patterns are imperceptibly slow. In production with thousands of records, the same code executes thousands of database round trips per page load.
What N+1 looks like in application logs:
-- In MySQL slow query log (or Laravel Debugbar / Django Debug Toolbar):
-- You see one query followed by many identical queries differing only in the WHERE value:
SELECT * FROM orders WHERE date = '2026-05-17'; -- 1 query returns 500 rows
SELECT * FROM customers WHERE id = 1; -- Query 2 of 501
SELECT * FROM customers WHERE id = 2; -- Query 3 of 501
SELECT * FROM customers WHERE id = 3; -- Query 4 of 501
-- ... repeated 500 times
-- Pattern to recognise: identical query structure, different WHERE value,
-- executing in rapid succession, count proportional to rows in parent query
Detecting N+1 in Laravel (Eloquent):
// Method 1: Laravel Debugbar (development only)
// composer require barryvdh/laravel-debugbar --dev
// Check the "Queries" tab -- shows total query count and each query
// N+1 appears as many near-identical queries with incrementing WHERE values
// Method 2: Laravel Telescope
// Logs all queries in development/staging; shows duplicates clearly
// Method 3: Enable query logging in code
\DB::enableQueryLog();
$orders = Order::all();
foreach ($orders as $order) {
echo $order->customer->name; // Triggers customer query for each order
}
$queries = \DB::getQueryLog();
echo count($queries); // Shows: 501 (1 + 500)
// THE FIX: eager loading with with()
$orders = Order::with('customer')->get();
// Now: 2 queries total (1 for orders, 1 for all related customers)
foreach ($orders as $order) {
echo $order->customer->name; // No additional query -- data already loaded
}
Detecting N+1 in Django:
# Django Debug Toolbar shows query count on each page
# Install: pip install django-debug-toolbar
# N+1 in Django ORM:
orders = Order.objects.filter(date='2026-05-17') # 1 query
for order in orders:
print(order.customer.name) # N queries -- triggers customer lookup per order
# Fix: select_related() for ForeignKey (JOIN in single query)
orders = Order.objects.filter(date='2026-05-17').select_related('customer')
# 1 query with JOIN -- fetches orders and customers together
# Fix: prefetch_related() for ManyToMany or reverse ForeignKey
orders = Order.objects.filter(date='2026-05-17').prefetch_related('items')
# 2 queries total -- orders query + 1 query for all related items
Detecting N+1 in Rails (ActiveRecord):
# Rails development log shows queries; use Bullet gem for N+1 detection
# gem 'bullet', group: :development
# Configure in config/environments/development.rb:
# config.after_initialize do
# Bullet.enable = true
# Bullet.alert = true
# Bullet.rails_logger = true
# end
# N+1 in Rails:
orders = Order.where(date: '2026-05-17') # 1 query
orders.each { |order| puts order.customer.name } # N queries
# Fix: includes() (equivalent to select_related / prefetch_related)
orders = Order.where(date: '2026-05-17').includes(:customer)
# 1-2 queries total
N+1 in raw SQL APIs:
-- Identify N+1 at the database level: queries with identical structure,
-- executed within milliseconds of each other, differing only in one WHERE value
-- MySQL: check processlist during a page load
SELECT id, time, state, info FROM information_schema.processlist
WHERE user = 'app_user' ORDER BY time DESC;
-- PostgreSQL: check pg_stat_activity
SELECT pid, query_start, query FROM pg_stat_activity
WHERE usename = 'app_user' AND state = 'active'
ORDER BY query_start DESC;
-- The fix for raw SQL: replace with IN() or JOIN:
-- Instead of: SELECT * FROM customers WHERE id = 1;
-- SELECT * FROM customers WHERE id = 2; (x 500)
-- Use: SELECT * FROM customers WHERE id IN (1, 2, 3, ..., 500);
-- Or: SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id
-- WHERE o.date = '2026-05-17';
In a performance investigation for a UK logistics company running a route management platform on Laravel and MySQL, the operations dashboard was loading in 8.4 seconds. The application used Eloquent ORM throughout. Laravel Debugbar showed 847 database queries per page load.
The dashboard loaded a list of 200 active routes. For each route, the template accessed $route->driver->name, $route->vehicle->registration, and $route->depot->city -- three separate relationships. Because none of these used eager loading, Laravel fired three separate queries per route: 200 routes x 3 relationships = 600 queries, plus the initial routes query and various navigation queries totalling 847.
The fix was a single change to the controller query:
// Before: 847 queries, 8.4 second page load
$routes = Route::where('status', 'active')->get();
// After: 4 queries, 0.9 second page load
$routes = Route::with(['driver', 'vehicle', 'depot'])
->where('status', 'active')
->get();
The four remaining queries after the fix were: one for routes (with status filter), one for all related drivers, one for all related vehicles, one for all related depots. Page load dropped from 8.4 seconds to 0.9 seconds. No index changes. No server configuration changes. One line of code changed in one controller method.
A second finding from the same audit: three additional pages had the same pattern with different models. The total fix involved changing four controller methods. Deployment to staging and production took 25 minutes. The client had been considering a server upgrade costing £200/month.
N+1 is the single most common performance problem in ORM-backed UK web applications. It is invisible in development (small datasets make 100 queries feel fast) and obvious in production (large datasets make 1,000 queries per page load catastrophic). For the broader diagnosis approach, see our database performance tuning guide.
Once slow queries are identified, some require structural rewriting rather than simply adding an index. The following patterns appear repeatedly in real-world database optimisation work.
JOINs vs subqueries:
-- Correlated subquery: executes once per row in outer query
-- If orders has 500,000 rows, the subquery executes 500,000 times
SELECT
order_id,
total_amount,
(SELECT customer_name FROM customers WHERE id = o.customer_id) AS customer_name
FROM orders o
WHERE order_date >= '2026-01-01';
-- JOIN equivalent: executes once (much faster)
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01';
-- Non-correlated subquery in WHERE (fine -- executes once, results cached)
SELECT order_id FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'GB' AND tier = 'premium'
);
-- Equivalent JOIN (often faster; easier to add composite index)
SELECT o.order_id
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'GB' AND c.tier = 'premium';
EXISTS vs IN for existence checks:
-- IN with subquery: loads all results into memory, then checks membership
-- Performance degrades with large subquery result sets
-- Also: IN returns empty set if subquery contains any NULL (subtle bug)
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM blocked_customers);
-- EXISTS: short-circuits on first match -- faster when blocked_customers is large
-- Also correctly handles NULLs in the subquery
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM blocked_customers bc WHERE bc.id = o.customer_id
);
-- NOT IN danger with NULLs:
-- If blocked_customers contains any NULL id, NOT IN returns zero rows (wrong result)
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT id FROM blocked_customers); -- RISKY
-- NOT EXISTS is safe with NULLs and typically faster:
SELECT * FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM blocked_customers bc WHERE bc.id = o.customer_id
);
Avoiding function calls on indexed columns:
-- Any function applied to an indexed column in a WHERE clause
-- prevents the index from being used
-- Bad: DATE() wrapper prevents index use on order_date
SELECT * FROM orders WHERE DATE(order_date) = '2026-05-17';
-- Fix: range condition uses the index
SELECT * FROM orders
WHERE order_date >= '2026-05-17' AND order_date < '2026-05-18';
-- Bad: LOWER() on indexed column prevents index use
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Fix: store emails in lowercase on insert/update
SELECT * FROM users WHERE email = 'user@example.com';
-- Or in PostgreSQL: use a functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; -- Now uses index
-- Bad: CAST on indexed column prevents index use in some databases
SELECT * FROM orders WHERE CAST(customer_id AS CHAR) = '12345';
-- Fix: match the column type
SELECT * FROM orders WHERE customer_id = 12345;
Efficient pagination -- keyset vs OFFSET:
-- Bad: OFFSET pagination degrades as page number increases
-- Page 500 requires reading and discarding 499 * 20 = 9,980 rows
SELECT * FROM orders ORDER BY order_id LIMIT 20 OFFSET 9980;
-- Good: keyset pagination uses an index seek (constant time regardless of page)
-- Client tracks the last order_id seen and passes it as the cursor
SELECT * FROM orders
WHERE order_id > :last_seen_id
ORDER BY order_id
LIMIT 20;
-- This query uses the primary key index -- same speed for page 1 or page 50,000
-- Multi-column keyset for non-unique sort columns:
SELECT * FROM orders
WHERE (order_date, order_id) > (:last_date, :last_id)
ORDER BY order_date, order_id
LIMIT 20;
Replacing COUNT(*) with approximate counts:
-- Exact COUNT(*) on large tables requires a full scan (no shortcut)
-- For UI elements showing approximate counts ("About 42,000 results"), exact is unnecessary
-- PostgreSQL: fast approximate count from table statistics
SELECT reltuples::bigint AS approx_count
FROM pg_class
WHERE relname = 'orders';
-- Returns in microseconds; accuracy within 10-20% of true count
-- Refreshed automatically by ANALYZE; call ANALYZE manually for fresh estimate
-- MySQL: approximate count from information_schema
SELECT TABLE_ROWS AS approx_count
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';
-- Less accurate than PostgreSQL's reltuples; use for display only
Application Performance Monitoring (APM) tools provide continuous visibility into database query performance without requiring manual slow query log analysis. They capture query execution times, identify regressions when new code is deployed, and alert on performance degradation before it becomes a user-facing outage.
Datadog APM for UK teams:
Datadog's database monitoring captures query execution plans automatically at configurable intervals, shows query volume and latency over time, and correlates slow database queries with application traces. For UK businesses with GDPR requirements, Datadog offers an EU region (eu1.datadoghq.com) that keeps data within the European Economic Area.
Key metrics to configure alerts on:
postgresql.query.time / mysql.performance.query_time: Alert when P95 exceeds 500mspostgresql.connections: Alert when connections exceed 80% of max_connectionspostgresql.deadlocks: Alert on any deadlock event (should be near zero)mysql.innodb.buffer_pool_hit_rate: Alert when below 95%pganalyze for PostgreSQL-specific analysis:
pganalyze is a PostgreSQL-specific monitoring tool that provides index recommendations, schema change impact analysis, VACUUM monitoring, and query performance tracking. It integrates directly with pg_stat_statements and provides significantly deeper PostgreSQL-specific insight than general APM tools. The index recommendations are generated from actual query patterns and are more reliable than manual index selection for complex schemas.
Percona Monitoring and Management (PMM) -- free option:
PMM is an open-source monitoring platform supporting MySQL, PostgreSQL, MongoDB and ProxySQL. It provides query analytics, slow query log integration, connection pool monitoring and server metrics dashboards via Grafana. For UK businesses with GDPR concerns about third-party SaaS tools processing query data, self-hosting PMM keeps all data within your own infrastructure at zero licensing cost.
# Deploy PMM Server via Docker
docker run -d -p 80:80 -p 443:443 \
--name pmm-server \
percona/pmm-server:latest
# Install PMM Client on the database server
wget https://downloads.percona.com/downloads/pmm2/latest/binary/tarball/pmm2-client-latest.tar.gz
# Follow Percona documentation for platform-specific client setup
# Add MySQL instance to monitoring
pmm-admin add mysql --username=pmm --password=pmmpassword --service-name=my-mysql
# Add PostgreSQL instance
pmm-admin add postgresql --username=pmm --password=pmmpassword --service-name=my-postgres
# Access PMM dashboard at http://your-server
# Default login: admin/admin (change immediately)
Specific metrics to track for UK production databases:
| Metric | Target | Alert Threshold | What It Indicates |
|---|---|---|---|
| Query P95 response time | < 200ms | > 500ms | 95% of queries return faster than this |
| Query P99 response time | < 1,000ms | > 2,000ms | Tail latency -- worst-case user experience |
| Buffer/cache hit rate | > 99% | < 95% | Data being read from disk instead of memory |
| Active connections | < 60% max | > 80% max | Connection pool pressure |
| Replication lag | < 1 second | > 10 seconds | Read replica falling behind primary |
| Deadlock rate | 0 | > 1/hour | Transaction ordering conflict in application code |
The most common causes of slow database queries, in order of frequency: missing index on a filtered column (causes full table scan, reading every row); N+1 patterns where application code runs hundreds of individual queries instead of one JOIN; functions applied to indexed columns in WHERE clauses (prevents index usage); SELECT * fetching all columns when only 2-3 are needed (prevents covering index usage); OFFSET-based pagination reading thousands of rows to skip past earlier pages; and lock contention where a long-running transaction blocks other queries waiting to access the same rows.
The most effective approaches for Laravel are: install the Laravel Debugbar package in development to see query count and execution time per page; enable the MySQL slow query log (slow_query_log = ON, long_query_time = 1) in staging or production to capture production-accurate data; use Laravel Telescope for query logging in staging; or add a DB::listen() call in AppServiceProvider to log all queries above a threshold during debugging. The combination of Debugbar in development and slow query log in production covers the full query visibility requirement for most Laravel applications.
N+1 is most common in ORM frameworks (Laravel, Django, Rails, Hibernate) because the ORM's lazy-loading default makes it easy to accidentally trigger individual queries per related object. However, N+1 patterns also appear in raw SQL code whenever a loop executes individual queries for each iteration. The pattern is the same regardless of how the SQL is generated: one query returns N results, then N additional queries fetch related data one at a time. The fix is always the same: replace the N individual queries with a single query using IN() or JOIN to fetch all related data in one round trip.
PostgreSQL's EXPLAIN ANALYZE is generally more accurate than MySQL's EXPLAIN because it actually executes the query and reports real timing and row counts. MySQL's EXPLAIN shows estimated values only (not actual), which can diverge significantly from reality when table statistics are stale. MySQL 8.0 added EXPLAIN ANALYZE (equivalent to PostgreSQL's) for more accurate plan inspection. For both databases, large discrepancies between estimated and actual row counts indicate stale table statistics -- run ANALYZE TABLE (MySQL) or ANALYZE table_name (PostgreSQL) to refresh them before making index decisions based on EXPLAIN output.
A typical slow query investigation and fix timeline: 1 hour to enable slow query logging and collect enough data (minimum 24-48 hours for production-representative data); 30-60 minutes to run EXPLAIN on the top 20 slow queries and identify the root cause (missing index, N+1, rewriting needed); 15-30 minutes to implement fixes (adding indexes or changing ORM calls); 1 hour for testing in staging. Most individual slow query fixes are completed within a half-day once the investigation data is available. The data collection period is the main elapsed time constraint, not the fix itself.
Finding and fixing slow database queries follows a consistent three-step process: enable slow query logging and collect 24-48 hours of production data; use EXPLAIN/EXPLAIN ANALYZE to identify root causes in the top 10 slow queries; apply fixes in order of impact (missing indexes first, N+1 patterns second, query rewrites third). The top 3 slow queries in any database-backed application almost always account for 50-70% of total database execution time. Fixing just those three queries -- typically a 2-4 hour task -- produces a visible performance improvement that hardware upgrades cannot match. For the complete context of database performance tuning including server configuration and monitoring, see our database performance tuning guide.
Softomate Solutions provides database query optimisation services for UK businesses from Stanmore, London. Get in touch for a free query performance assessment.
Written by the Softomate Solutions technical team, database and software specialists based in Stanmore, London.
More from the blog
Let us help
Talk to our London-based team about how we can build the AI software, automation, or bespoke development tailored to your needs.
Softomate Solutions Limited
Deen Dayal Yadav
Online
